CODEWITHSUNDEEP
sqlsql-serversql-order-by
xam developer
xam developer

Reputation: 1983

Specific Ordering in SQL

I have a SQL Server 2008 database. In this database, I have a result set that looks like the following:

ID  Name          Department    LastOrderDate
--  ----          ----------    -------------
1   Golf Balls    Sports        01/01/2015
2   Compact Disc  Electronics   02/01/2015
3   Tires         Automotive    01/15/2015
4   T-Shirt       Clothing      01/10/2015
5   DVD           Electronics   01/07/2015
6   Tennis Balls  Sports        01/09/2015   
7   Sweatshirt    Clothing      01/04/2015
...

For some reason, my users want to get the results ordered by department, then last order date. However, not by department name. Instead, the departments will be in a specific order. For example, they want to see the results ordered by Electronics, Automotive, Sports, then Clothing. To throw another kink in works, I cannot update the table schema.

Is there a way to do this with a SQL Query? If so, how? Currently, I'm stuck at

SELECT *
FROM
  vOrders o
ORDER BY
  o.LastOrderDate

Thank you!

Upvotes: 1

Views: 70

Answers (3)

Hiten004
Hiten004

Reputation: 2481

Here solution with CTE

with c (iOrder, dept) 
as (
Select 1, 'Electronics'
Union
Select 2, 'Automotive'
Union
Select 3, 'Sports'
Union
Select 4, 'Clothing'
)
Select * from c
SELECT o.*
FROM
  vOrders o join c
  on c.dept = o.Department    
ORDER BY
  c.iOrder

Upvotes: 0

potashin
potashin

Reputation: 44581

You can use case expression ;

order by case when department = 'Electronics' then 1
              when department = 'Automotive' then 2
              when department = 'Sports' then 3
              when department = 'Clothing' then 4
              else 5 end

Upvotes: 2

Z .
Z .

Reputation: 12837

create a table for the departments that has the name (or better id) of the department and the display order. then join to that table and order by the display order column.

alternatively you can do a order by case:

ORDER BY CASE WHEN Department = 'Electronics' THEN 1
     WHEN Department = 'Automotive' THEN 2
...
END

(that is not recommended for larger tables)

Upvotes: 1

Related Questions