SQL 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)

SQL Order By

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-12 : 09:00:51

Brad writes "Is there a way i can order by specific entries? (not ordering in alphabetical order?)

Example,

field values:

Subcat
=========
broadband
broadband
broadband
wireless
wireless
wireless
usb
usb
usb

is there a way i can order wireless first broadband second and usb third all in a stored procedure?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 09:35:38

You can use a CASE statement to evaluate the SubCat column and generate the sort value you want:

SELECT SubCat FROM myTable
ORDER BY CASE SubCat
WHEN 'broadband' THEN -2
WHEN 'wireless' THEN -1
WHEN 'usb' THEN 0
ELSE 1 END

You can use any values you want; I like to use negative numbers for the pre-defined sort orders, then have the rest sorted with positive numbers.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-12 : 10:37:38

or make the sort order data driven by adding an (int) column to the subcat table, populating it with the rank of the particular description, and specifying it in your order by.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources