How to sort a table with some fields positioned according to my conditions

Question

I want to sort a table in sql server. Condition that i need to fulfill is this I have a table that has some records in it like this

Select One
None
Child
Old
Neutral
..
..
..

i want it to be sorted in such a way that Select One comes up and None comes at the end and remaining gets sorted alphabetically.

Select One
Child
Neutral
Old
..
..
..
None

Is it possible some how to do this in any efficient manner. I have few other fields also associated with it. On behalf of these I want to get those values. These fields are to be filled in comboboxes

I have to do this for 12 tables.

Answer 1

You could add a sequence field to the table - a tinyint which you could use in an order by clause:

select field_name
  from table_name
  order by [sequence]

Or you could use a case statement to create a sort field:

select field_name, 
  case field_name
    when 'Select One' then 1
    when 'Child' then 2
    when 'Neutral' then 3
    when...
  end as sort_field
 from table_name
 order by sort_field

Answer 2

As these are values that are going to be used for some selection it is common to store such values in tables of their own.

From your question I gather that you are already doing that.

Just add another field that will define sort order, then you can select and order by that field.

There are ways to do this without extra column (assumin the field names is value)

ORDER BY CASE value 
            WHEN 'None' THEN 'ZZZZZ' 
            WHEN 'Select One' THEN 'AAAAA' 
            ELSE values 
         END

(Replace 'AAAAA' and 'ZZZZZ' with appropriate constants for your domain), but I doubt that this approach will be any better in terms of performance or ease of implementation/maintenance.

Answer 3

Try using a CASE in the ORDER BY like this:

SELECT...

    ORDER BY CASE
                 WHEN YourColumn='Select One' then 1
                 WHEN YourColumn='none' then 3
                 ELSE 2
              END,YourColumn

working example:

DECLARE @YourTable table (YourColumn varchar(20))
INSERT @YourTable VALUES ('Select One')
INSERT @YourTable VALUES ('None')
INSERT @YourTable VALUES ('Child')
INSERT @YourTable VALUES ('Old')
INSERT @YourTable VALUES ('Neutral')

SELECT * FROM @YourTable
ORDER BY CASE
             WHEN YourColumn='Select One' then 1
             WHEN YourColumn='none' then 3
             ELSE 2
          END,YourColumn

OUTPUT:

YourColumn
--------------------
Select One
Child
Neutral
Old
None

(5 row(s) affected)