Order by number - 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)

Order by number

Author

Topic

dimitri2476
Starting Member

3 Posts

Posted - 2002-11-25 : 09:37:56

I have table ( Contact ) with a column( Rank ) and it has only values like 1, 2, 3.

How do sort the table by a specific number.

Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-25 : 09:44:49

Select * from contact order by rank

?

-------
Moo.

dimitri2476
Starting Member

3 Posts

Posted - 2002-11-25 : 09:48:40

What if i want the results to come back first 2, 1 ,3

quote:

Select * from contact order by rank

?

-------
Moo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-25 : 09:50:01

Select * FROM Contact
ORDER BY
CASE Rank
WHEN 1 THEN 2
WHEN 2 THEN 1
WHEN 3 THEN 3
END

Edited by - ValterBorges on 11/25/2002 09:51:06

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-25 : 09:53:19

Something like

Select * from contact
ORDER BY
case when rank =2 then 0 else 1 end , rank

-------
Moo.

dimitri2476
Starting Member

3 Posts

Posted - 2002-11-25 : 10:00:56

Thanks

you are correct

quote:

Select * FROM Contact
ORDER BY
CASE Rank
WHEN 1 THEN 2
WHEN 2 THEN 1
WHEN 3 THEN 3
END

Edited by - ValterBorges on 11/25/2002 09:51:06

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-25 : 11:10:29

I these cases, create a table with how you want this values to be ordered.

Value OrderValue
1 2
2 1
3 3

Then link to this table and order by OrderValue. This way, you can use this information in multiple queries. Also, indexes will be used as well. Finally, if you want to order the values this way, there is probably other info you wish to store with these values so this would be the place to do it.

As always, I am against hard-coding rules like this in SQL
statements; move them out into easily maintained and re-used tables.

- Jeff

Edited by - jsmith8858 on 11/25/2002 11:24:22

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-25 : 14:26:45

I agree when you only have a few orders, however when the order could be combinatorial then you're left to writing some dynamic sql with the case statement, or by creating a temp table with the order, or creating multiple columns for each order

.

I would first try the case that always works (Dynamic SQL) and then if performance becomes an issue optimize by using the method you describe with ranking tables where the need arises.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources