Customized sorting - 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)

Customized sorting

Author

Topic

Will
Starting Member

1 Post

Posted - 2005-06-21 : 09:45:52

We have a financial report that will always generate 10 lines of data.
I need the lines to come out in a specific order. For example : Any Ideas how to make the results set of alice, bob raymond and wilard come out in raymond, wilard, alice, bob order? I want to associate a numeric value with each enter but suppres it and still have the sequence that I need.

jhermiz

3564 Posts

Posted - 2005-06-21 : 10:38:31

SELECT Name FROM Table1
ORDER BY
CASE
WHEN Name='raymond' then 1,
WHEN Name='wilard' then 2,
WHEN Name='alice' then 3,
WHEN Name='bob' then 4
END

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-21 : 10:48:10

Jon, I think commas are not necessary

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-21 : 10:55:18

If these sort values are important and persistant, store those values in your database in whatever table defines those names. Then simply sort by that value. This makes your code clearer, more efficient, and easier to maintain.

If you cannot alter the DB, then as Jon demonstrates a CASE may be the way to go.

- Jeff

jhermiz

3564 Posts

Posted - 2005-06-21 : 11:11:57

quote:
Originally posted by madhivanan

Jon, I think commas are not necessary

Madhivanan

Failing to plan is Planning to fail

Sorry:

SELECT Name FROM Table1
ORDER BY
CASE
WHEN Name='raymond' then 1
WHEN Name='wilard' then 2
WHEN Name='alice' then 3
WHEN Name='bob' then 4
END

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources