Custom sort in SQL Server

Ma tju

Hi, I have a table where the results are sorted using an "priority" column, eg:

Doc_Value priority

aaa 0
xxx 1
bbb 3
ccc 0
aaa 2

I need the SQL results to be in a specific order based on the "priority", but not in ascending or descending order. The order that I want them in is 1,2,3,0,0,0,0.... Highest priority start with 1 to infinite (9999) number but the lowest priority is 0... Any suggestion guys? :)

ma tju Software Application Engineer Petaling Jaya,Selangor, Malaysia Ring Master SB MVP 2008 ;p Petaling Jaya MOP (Otai)

Mycroft Holmes

Use a case statement in your order by clause

order by case when priority = 0 then 9999 else sequence end

Never underestimate the power of human stupidity RAH

Ma tju

Thanks a lot its working. +5 :-D

ma tju Software Application Engineer Petaling Jaya,Selangor, Malaysia Ring Master SB MVP 2008 ;p Petaling Jaya MOP (Otai)

Lost User

Introduce a new table called PriorityOrder with something like:

Priority PriorityOrder

0 5
1 1
2 2
3 3
4 4

And then join your tables with this table on the Priority column and then Order by the PriorityOrder column. This design gives you the flexibility to change the ordering any time by changing the contents of this table instead of changing code.

Mycroft Holmes

Wow what a nasty solution, he simply wants to move the 0 value records to the end of the sort and you want to create and support another table. So every time a new record is created he potentially needs to update the priority table.

Never underestimate the power of human stupidity RAH

Lost User

Priorities are usually limited and may be used in more than one table. This is a generic solution which works best if Priority is stored in its own reference table (a.k.a. Master table), in which case the PriorityOrder column can appear in the same table.

Ma tju

Hi Shameel, Thanks for your reply to my problem. :-D Like Mycroft Holmes said, I just want a simply to move the 0 value records to the end of the sort. Regards.

ma tju Software Application Engineer Petaling Jaya,Selangor, Malaysia Ring Master SB MVP 2008 ;p Petaling Jaya MOP (Otai)

Niladri_Biswas

Hi, Mycroft's solution is elegant and I like the way he presents the solution. However, there are a few more ways which will accomplish the task, though again I like Mycroft's solution

declare @t table(docvalue varchar(50),priority int)
insert into @t select 'aaa',0 union all select 'xxx', 1 union all select 'bbb', 3 union all
select 'ccc',0 union all select 'aaa',2

Query1:

select docvalue,priority from @t where priority <> 0 group by priority,docvalue
union all
select * from @t where priority = 0

Query 2:

select distinct * from @t where priority <> 0 group by priority,docvalue
union
select * from @t where priority = 0

Output:

docvalue priority
xxx 1
aaa 2
bbb 3
aaa 0
ccc 0

Niladri Biswas