CODEWITHSUNDEEP
sqlsql-serversql-order-by
Sujith Kp
Sujith Kp

Reputation: 1105

order rows based on some sequence

I have a table like this

ID   Subject       StatusCd     Date
------------------------------------------
1    abc           3            01/03/2013
2    def           1            01/03/2013
3    ghi           2            05/03/2013
4    jkl           3            02/03/2013
5    mno           3            06/03/2013

How can I write a SQL query which will order rows in this sequence of StatusCd (3, 1, 2). Also I want to sort each row in a group based on its Date.

Upvotes: 0

Views: 87

Answers (2)

TechDo
TechDo

Reputation: 18629

Please try:

select 
    ID, 
    [Subject], 
    StatusCd, 
    [Date] 
from
    YourTable
order by case StatusCd when 3 then 0 else StatusCd end, 
    [date]

Upvotes: 2

Taryn
Taryn

Reputation: 247690

You can use a CASE expression the the ORDER BY clause:

select id, subject, statuscd, date
from yt
order by 
  case statuscd
    when 3 then 0
    when 1 then 1
    when 2 then 2
  end, date

See SQL Fiddle with Demo. This gives a result:

| ID | SUBJECT | STATUSCD |       DATE |
----------------------------------------
|  1 |     abc |        3 | 2013-01-03 |
|  4 |     jkl |        3 | 2013-02-03 |
|  5 |     mno |        3 | 2013-06-03 |
|  2 |     def |        1 | 2013-01-03 |
|  3 |     ghi |        2 | 2013-05-03 |

Upvotes: 3

Related Questions