A Special Order By clause?

The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?

SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1
ORDER BY Country_Long

I think this will work with you:

 
1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO
 

Good luck.

|||

You probably need to do 2 SELECTs and do a UNION. The first one will have 'United States' followed by the rest in alphabetical order.

|||

I read the question wrong

What CS4Ever and ndinakar both have posted is how you want to do this.

|||

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

|||

SolitaryMan:

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

Your are welcome SolitaryMan.

Thanks for posting another example. Actullay there is always more than a way to do the thing.

I believe (not sure) the example in my previous post in better in term of performance, because of the CASE in the ORDER BY clause for the example you menioned. Since database engine will check the case of each record (long processing) while this is not the case in my example.

Again, thanks for sharing the example.

|||

CS4Ever:

I think this will work with you:

 
1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO
 

Good luck.

Change the order by clause to:

1ORDER BY 1, Country_Long

Good luck.

A severe error occurred on the current command.

I am having problems with an error that I keep receiving. The error message states "A severe error occurred on the current command. The results, if any, should be discarded."

This error is popping up when I try to call a specific stored procedure in SQL2005. The error is not specific to ASP.NET code because it also occurs if I try to run the procedure in SQL Server Management Studio. The procedure is exactly the same that is running on an SQL2000 server on a live site, so it apparently is specific to SQL2005.

I have not worked much with SQL2005 or SQL cursors in the past and this code was not written by me so I am not sure where to start making changes, if any are needed.

The procedure is as follows:

ALTER PROCEDURE [dbo].[spGetAdmins]
@.userid int,
@.forDate datetime
AS

DECLARE @.lngTabCount INTEGER,
@.lngLoopCount INTEGER,
@.type varchar(15),
@.id varchar(20),
@.statement varchar(1000),
@.useridLocal int,
@.forDateLocal datetime

SET @.useridLocal = @.userid
SET @.forDateLocal = @.forDate

DECLARE @.AdminInfo TABLE (AdminID varchar(20), AdminName varchar(50))

/* check parameters */
IF (@.useridLocal <= 0)
BEGIN
RAISERROR( 'Invalid UserID (%d)', 16, 1, @.useridLocal )
END
IF (@.forDateLocal IS NULL)
BEGIN
SELECT @.forDateLocal = GETDATE()
END

DECLARE profInfo CURSOR
LOCAL FORWARD_ONLY
FOR
SELECT ProfileType, ProfileValue
FROM tblCSUserProfile
WHERE UserID = @.useridLocal and
(StartDate is not null or StartDate <= @.forDateLocal ) and
(EndDate is null or EndDate > @.forDateLocal )
ORDER BY ProfileType

OPEN profInfo
FETCH profInfo
INTO @.type, @.id

WHILE @.@.Fetch_Status = 0
BEGIN
IF @.id = '*'
BEGIN
INSERT INTO @.AdminInfo
VALUES ('*', '* Everything')
END
ELSE
BEGIN
INSERT INTO @.AdminInfo
SELECT adm.[ADM_CODE], adm.[ADMIN_NAM]
FROM OPENROWSET('SQLOLEDB', 'server'; 'database'; 'password', [table]) AS adm
WHERE adm.[ADM_CODE] = @.id
END

FETCH profInfo INTO @.type, @.id

END

CLOSE profInfo

DEALLOCATE profInfo

SELECT DISTINCT(UPPER(AdminID)) AS AdminCode
FROM @.AdminInfo
GROUP BY AdminID
ORDER BY AdminCode

(1) I would change the line

FETCH profInfo
INTO @.type, @.id

to

FETCH NEXT FROM profInfo INTO @.type, @.id

and

FETCH profInfo INTO @.type, @.id

to

FETCH NEXT FROM profInfo INTO @.type, @.id

I would also recommend using a table variable instead of cursors.

(2) Check out the syntax for OPENROWSET in BOL.

Try using the SQLNCLI (SQL Native Client) instead of SQLOLEDB and see if it makes a difference.