CODEWITHSUNDEEP
sqlsql-server-2008
JJunior
JJunior

Reputation: 2849

Ordering resultset in specific order

I need to group my resultset with "NY" entries first then any other state entries which might be returned.

Example:

OrderID   State   Type   
----------------------------           
123       CA      Checque
123       NY      ELectronic
222       JC      Checque
123       NY      Checque

OrderID is the primary key

Result set needs to look like:

OrderID   State   Type    
----------------------------
123       NY      ELectronic
123       NY      Checque     
123       CA      Checque

For user with OrderID = 123

It works if I do below but Is it possible to do the same without using union.

I am doing this for a large set of records.

It works using:

Select * from Table1 where orderid = 123 and State = 'NY'
Union
Select * from Table1 where orderid = 123 and State <> 'NY'

Using SQL Server 2008

Thanks

Upvotes: 0

Views: 49

Answers (2)

Kaf
Kaf

Reputation: 33809

You can use a case statement to get 'NY' to the top and then by State and Type desc like below:

FIDDLE DEMO

select OrderID, State, Type
from T
where orderID = 123
order by case when State = 'NY' then '0' else State end, Type desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Try this at the end of your query:

order by (case when state = 'NY' then 0 else 1 end),
         state

The fact that the union works is happenstance -- not at all guaranteed. The only way to order results is by specifying an order by clause at the outermost level of a query.

Upvotes: 2

Related Questions