Special 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

Transact-SQL (2000)

Special Order By

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-04-23 : 13:14:14

I have a table with two columns - Name and Serial_No

Serial_No is an number assigned to a product. It's defined internally for a special purpose and is not related with any other tables. Some products have unknown Serial_No, then they are assigned with 0. Others are assigned with any numbers from 1 to 99.

Usually we return a list in the order of Serial_No -

Query: Select Name, Serial_No order by Serial_No
Resultset:

DC-80, 0
DF-11, 0
DS-10, 1
DE-32, 1
DD-02, 2
DG-01, 99

Since Serial_No 0 is not as important as others, we want it at the very end of the lists after 99, and others are still in an ASC order of Serial_No.

The resultset then should look like -

DS-10, 1
DE-32, 1
DD-02, 2
DG-01, 99
DC-80, 0
DF-11, 0

Can anybody help me this with one query. Thanks in advance.

George

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-23 : 13:26:31

Try doing a (forum) search for CASE + ORDER BY around here.

This sort of thing has come up before.....usually when people are trying to get null's to one end of a report or another....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 13:57:47

Select Name, Serial_No
from ...
order by
case when serial_no = 0 then 1 else 0 end ASC,
Serial_No ASC

- Jeff

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-04-23 : 14:42:51

Kewl...thanks everyone.

George

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 14:46:26

Here's an even "kewler" one:

Select Name, Serial_No
from ...
order by
SIGN(serial_no) DESC,
Serial_No ASC

might be more efficient .... it depends if CASE logic is faster or slower than a function like SIGN() ...

- Jeff

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-04-23 : 15:05:34

Great...never thought it could be done that way. Thank you very much Jeff.

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources