How to sort a table with some fields arranged according to my conditions
I want to sort a table in sql server. The condition that I need to fulfill is I have a table that has records in this view
Select One
None
Child
Old
Neutral
..
..
..
I want it to be sorted such that Select One appears and None at the end, and the rest are sorted alphabetically.
Select One
Child
Neutral
Old
..
..
..
None
Is it possible how to do this in any efficient way. I have a few more fields related to it. On behalf of these, I want to receive these values. These fields must be filled in comboboxes
I have to do this for 12 tables.
a source to share
Try using CASE in 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)
a source to share
You can add a sequence field to the table - tinyint, which can be used in the order by clause:
select field_name
from table_name
order by [sequence]
Or you can 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
a source to share
Since these are values to be used for some selection, it is common to store such values in their own tables.
From your question, I understand that you are already doing this.
Just add another field that will determine the sort order, then you can select and order by that field.
There are ways to do this without an extra column (know the field names value
)
ORDER BY CASE value
WHEN 'None' THEN 'ZZZZZ'
WHEN 'Select One' THEN 'AAAAA'
ELSE values
END
(Replace 'AAAAA'
and 'ZZZZZ'
with the appropriate constants for your domain), but I doubt this approach would be any better in terms of performance or ease of implementation / maintenance.
a source to share