Implement Partial sorted query in sql server 2005

Question

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 stateID = 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

Answer 1

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

Answer 2

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