T-SQL help - order by a particular value

ringo747 · 23 Aug 2011 at 13:37

I have a fairly huge T-SQL stored proc that I need to update for a colleague. There are a load of different values in one column such as open, created, pending etc.

I want to sort the results so that 'pending' is returned first. Obviously I can't use ASC or DESC here. Is there any easy, quick and obvious way to do this that I am missing completely?

GravyMonster · 23 Aug 2011 at 14:06

How I normally do this is to dump the data into a temp table, add a column for rank, then update the rank column with an int for each type, in your case pending = 1, created = 2, open = 3.

Then SELECT * FROM @TempTable ORDER BY Rank

AshenShugar1873 · 23 Aug 2011 at 14:18

Why not just create an OrderBy table with two columns: Type (String) and Order (Number). Populate with all the types and add a numeric order for each. Then join the OrderBy table back to your main table using Type and sort by Order.

topdog@OC · 23 Aug 2011 at 14:40

create table misc_stuff(status nvarchar(16))
go

insert into misc_stuff (status) values ('pending')
go
insert into misc_stuff (status) values ('created')
go
insert into misc_stuff (status) values ('open')
go
insert into misc_stuff (status) values ('pending')
go
insert into misc_stuff (status) values ('created')
go
insert into misc_stuff (status) values ('open')
go

select
case when status = 'pending' then 1
when status = 'created' then 2
when status = 'open' then 3 end as rank,
status
from misc_stuff
order by rank
go

drop table misc_stuff
go

topdog@OC · 23 Aug 2011 at 14:41

Results:

rank status
------- ---------
1 pending
1 pending
2 created
2 created
3 open
3 open

6 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

Edit: If you don't care about the ordering of the non-Pending rows then just drop those ranks and have a catch-all 'else' instead of 'when', I suspect you might have a preference for order the other values follow in too, but your specs were only for pending to be sorted first so unsure.

ringo747 · 23 Aug 2011 at 15:14

Thanks I got this sorted in the meantime. I used a ORDER BY CASE WHEN something THEN 1 ELSE 2 END, fieldname. Found it in google and it worked.

topdog@OC · 23 Aug 2011 at 15:29

Yeah, same deal I guess as far as the DBMS' optimizer is concerned.

Better (if it can do this in memory, and treat it deterministically,) than doing unnecessary I/O with temp or physical tables IMO though either way.