Custom sort order 1

[wuxapian]

Hi all,

I need to be able to force a query to order records by a certain field in an order I specify.

For example I have records where the descriptions Could be "Cow", "Helicoptor", "Fish" but when I query the database I want to order them by "Fish", "Helicoptor", "Cow". Does anyone know if there is a way of specifying your own sort order?

TIA
Wux.

 

[r937]

how many animals are we talking about?

ORDER BY
case
when descr = 'Fish' then 1
when descr = 'Helicopter' then 2
when descr = 'Cow' then 3
else 0
end

rudy

 

[wuxapian]

Thanks Rudy, that was exactly what I needed.

Wux.

 

[SQLSister]

If you are going to do this with a long list, then it is best to put a SortOrder column in your table and then set the order in that and use an order by statement to get your results to use this column for the order.