Changing the order by? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Changing the order by?

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-18 : 16:00:49

Hi,

I want to change the way sql orders order by. I have a column that is set to NotNull. Some rows have entries and some only have ''.

I would like to order by the entries first and then the '' rows second.

Exaple using the "Comapny" column....

CREATE TABLE #Info (
ID int IDENTITY,
Company nvarchar(15),
FirstName nvarchar(5),
LastName nvarchar(7),
City nvarchar(9),
State nvarchar(2)
)

INSERT INTO #Info (Company, FirstName, LastName, City, State)
VALUES( 'Arcom Welding', 'Joe', 'Glenn', 'Stratford', 'MN')

INSERT INTO #Info (Company, FirstName, LastName, City, State)
VALUES( '', 'Glenn', 'Barbigg', 'Bantar', 'CT')

INSERT INTO #Info (Company, FirstName, LastName, City, State)
VALUES( 'Baroom Painting', 'John', 'Billy', 'Amstram', 'ID')

SELECT *
FROM #Info
ORDER BY Company, LastName, FirstName

DROP TABLE #Info

Returns this...

ID Company FirstName LastName City State
----------- --------------- --------- -------- --------- -----
2 Glenn Barbigg Bantar CT
1 Arcom Welding Joe Glenn Stratford MN
3 Baroom Painting John Billy Amstram ID

I want it to return this

ID Company FirstName LastName City State
----------- --------------- --------- -------- --------- -----
1 Arcom Welding Joe Glenn Stratford MN
3 Baroom Painting John Billy Amstram ID
2 Glenn Barbigg Bantar CT

Is there a way to change the order by so that entries are selected first and that '' are last.

Thanks,

JB

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-18 : 16:56:48

ORDER BY
CASE
WHEN Column <> '' then 1
ELSE 2
END

Edited by - ValterBorges on 12/18/2002 16:57:28

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-18 : 17:10:21

Or, for each column:

ORDER BY
CASE WHEN Company = '' THEN 'ZZZZZ' ELSE Company END,
CASE WHEN LastName = '' THEN 'ZZZZZ' ELSE LastName END,
CASE WHEN FirstName ='' THEN 'ZZZZZ' ELSE FirstName END

or something along those lines.

- Jeff

Edited by - jsmith8858 on 12/18/2002 17:12:19

JBelthoff
Posting Yak Master

173 Posts

Posted - 2002-12-18 : 17:20:23

Thanks guys!

JB

Edited by - JBelthoff on 12/18/2002 17:29:54

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources