Implement Partial sorted query in sql server 2005
I have to show records in such a way that some selected records should come first. After this, another records come in sorted manner from the same table.
For example, If I select state having stat开发者_Python百科eID = 5 then the corresponding record should come first. after this another records should come in sorted manner.
For this, I tried union but it shows all in sorted.
select state from statemaster where stateid=5
union all
select state from statemaster
where not stateid =5
order by state
Thanks
This one will use CASE
to give you states with stateid = 5
first, followed by the rest. Second sort-criteria is the state
.
Select state
From statemaster
Order By
Case When stateid = 5 Then 0 Else 1 End,
state
This will be useful if you have more than 2 unions
select 1 as sort_id, state from statemaster where stateid=5
union all
select 2 as sort_id, state from statemaster
where stateid between 1 and 4
union all
select 3 as sort_id, state from statemaster
where stateid > 5
order by sort_id, state
精彩评论