Custom sort order in a SQL statement

May 25, 2007

I tracked a status of a record in a text field and wanted to sort the results in a specific order. For example I want “active” to show before “rejected” and “deleted” to show up last, etc… It turns out it was pretty easy to do in SQL. Here it is:

select * from viewoffers where itemid=@ItemId

order by

    case status

    when ‘active’ then 1

    when ‘rejected’ then 2

    else 99

end

Posted in SQL