Styjun

I have a simple form of SELECT ORDER BY query which is using CASE for creating sorted id's.
I referred two approaches based on https://gist.github.com/cpjolicoeur/3590737 and used one of them.

SELECT id FROM tasks t ORDER BY 
CASE 
WHEN t.type = 'TypeA' THEN 1 
WHEN t.type = 'TypeB' THEN 2 
WHEN t.type = 'TypeC' THEN 3 
END, 
CASE 
WHEN t.state = 'Interrupted' THEN 4 
WHEN t.state = 'Terminated' THEN 5 
WHEN t.state = 'Completed' THEN 6
WHEN t.state = 'Killed' THEN 7
WHEN t.state = 'Warning' THEN 8
WHEN t.state = 'Starting' THEN 9
WHEN t.state = 'New' THEN 10
WHEN t.state = 'Running' THEN 11
END, 
modified ASC;

The above query is based on below criteria to sort ids:

1. TypeA with state:
   Interrupted,Terminated,Completed,Killed,Warning,Starting,New,Running


2. TypeB with state:
   Interrupted,Terminated,Completed,Killed,Warning,Starting,New,Running


3. TypeC with state:
   Interrupted,Terminated,Completed,Killed,Warning,Starting,New,Running

I need to modify this sql for below sorting criteria now:

1. TypeA with state: Interrupted,Terminated,Completed,Killed


2. TypeB with state: Interrupted,Terminated,Completed,Killed


3. TypeC with state: Interrupted,Terminated


4. TypeA with state: Warning,Starting,New,Running


5. TypeB with state: Warning,Starting,New,Running


6. TypeC with state: Completed,Killed,Warning,Starting,New,Running

Please note the ordering of 'state'(as specified) is equally important here for a 'type'

I am trying to modify above sql so that it meets the new criteria. Being not an sql expert I am looking for most efficient way to rewrite this sql which could also accommodate slight changes on sorting criteria in near future.

To further clarify sharing example below:

sample data

id | type | state
----+-------+-------------
 1 | TypeC | Completed
 2 | TypeA | Completed
 3 | TypeA | Running
 4 | TypeB | Completed
 5 | TypeB | Running
 6 | TypeC | Terminated
 7 | TypeC | Unknown
 8 | TypeA | Completed
 9 | TypeB | Interrupted
 10 | TypeB | Completed
 11 | TypeB | Interrupted
 12 | TypeC | Killed
 13 | TypeC | Running
 14 | TypeB | Warning
 15 | TypeB | Running
 16 | TypeB | Killed

expected data

id | type | state
--- +-------+-------------
 1 | TypeA | Completed
 8 | TypeA | Completed
 9 | TypeB | Interrupted
 11 | TypeB | Interrupted
 4 | TypeB | Completed
 10 | TypeB | Completed
 16 | TypeB | Killed
 7 | TypeC | Unknown
 6 | TypeC | Terminated
 3 | TypeA | Running
 14 | TypeB | Warning
 5 | TypeB | Running
 15 | TypeB | Running
 1 | TypeC | Completed
 12 | TypeC | Killed
 13 | TypeC | Running

Maybe something like this?

CASE 
WHEN t.type = 'TypeA' AND t.state 
 IN ( 'Interrupted','Terminated','Completed','Killed') 
 THEN 1
WHEN t.type = 'TypeB' AND t.state 
 IN ( 'Interrupted','Terminated','Completed','Killed') 
 THEN 2
..
..

EDIT

I would recommend you to create a hierarchy table, this way your query gets simpler.

CREATE TABLE type_state_hier AS
select * FROM 
(
 VALUES
(1,'TypeA','Interrupted'),
(2,'TypeA','Terminated'),
(3,'TypeA','Completed'),
(4,'TypeA','Killed'),
(5,'TypeB','Interrupted'),
(6,'TypeB','Terminated'),
(7,'TypeB','Completed'),
(8,'TypeB','Killed'),
(9,'TypeC','Interrupted'),
(10,'TypeC','Terminated'),
(11,'TypeA','Warning'),
(12,'TypeA','Starting'),
(13,'TypeA','New'),
(14,'TypeA','Running'),
(15,'TypeB','Warning'),
(16,'TypeB','Starting'),
(17,'TypeB','New'),
(18,'TypeB','Running'),
(19,'TypeC','Completed'),
(20,'TypeC','Killed'),
(21,'TypeC','Warning'),
(22,'TypeC','Starting'),
(23,'TypeC','New'),
(24,'TypeC','Running')
) As s(rnk,type,state)
;

Now, query it with a simple join with your main table.

select t.* from tasks t 
 join type_state_hier h on 
 h.type=t.type and h.state = t.state
order by h.rnk ,t.id

If you don't want to create a table, simply use it as a CTE (refer demo)

DEMO