SQL Custom sorting

vbnetcoder
vbnetcoder used Ask the Experts™
on
I need to be able to custom sort values that come from a query basically i have a query like this


SELECT Name from Table

and i want to be able to custom order the values

Ideally, it would add an extra field to the SQL query at runtime so the result would be something like this

Name             SortOrder
------------------------------------
John               1
Sam               2
Susan           3
Andy            4
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Expand on 'custom sort'.  Are you trying to get an incrementing integer value for each name, or establishing a sort order other than A-Z name?

Author

Commented:
establishing a sort order other than A-Z name?

Author

Commented:
sorry: My comment was a statement in answer to your question not intended to be a question.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
If the names are fixed then you can do something like this...
SELECT blah, blah, blah
FROM your_table
ORDER BY CASE
   WHEN name='John' THEN 1
   WHEN name='Sam' THEN 2
   WHEN name='Susan' THEN 3
   WHEN name='Andy' THEN 4 END

Open in new window


An illustration can be found in SQL Server CASE Solutions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial