Deploy Partial Sorted Query in SQL Server 2005

Question

I need to show records in such a way that some selected records should appear in the first place. After that, other records are sorted from one table.

For example, if I select a state with a state of IDID = 5, then the corresponding record should be the first. after that, the other entries must arrive in a sorted manner.

For this, I tried to combine, but it shows everything in sorted order.

select state from statemaster where stateid=5 union all select state from statemaster where not stateid =5 order by state 

thanks

Answer 1

This one will use CASE to first indicate states with stateid = 5 , and then the rest. The second sorting criterion is state .

 Select state From statemaster Order By Case When stateid = 5 Then 0 Else 1 End, state 

Answer 2

This will be useful if you have more than two 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