CODEWITHSUNDEEP
sqlpostgresql
HOOOPLA
HOOOPLA

Reputation: 57

Postgresql Order by based on specific values

I have a column called item_priority Which has following values 'important', 'super_important', 'important_1', 'important_2'

I want to order the result in a way where the following items

'important', 'super_important', 'important_1', 'important_2'

Appear in order.

This is my query, it appears that important_2 appears before important_1, currently.

SELECT * from 
main_table
where product_id = '200'
ORDER BY col1, col2, CASE WHEN item_priority ~ '^[a-zA-Z]' THEN 1 WHEN WHEN item_priority~ '^[0-9]' THEN 2 END, item_priority desc, col3

Upvotes: 1

Views: 1560

Answers (2)

SQLpro
SQLpro

Reputation: 5177

Ordering by an array will be especially slow.

Just use a valued CASE like this:

SELECT * 
FROM   main_table
WHERE  product_id = '200'
ORDER  BY col1, col2, 
          CASE item_priority 
             WHEN 'important' THEN 1
             WHEN 'super_important' THEN 2
             WHEN 'important_1' THEN 3
             WHEN 'important_2' THEN 4   
          END;

En fact, valued CASE is quicker than any other forms of ordering...

Upvotes: 4

AdamKG
AdamKG

Reputation: 14091

ORDER BY supports arbitrary expressions. array_position() will do what you're looking for, making the final clause ORDER BY array_position(['important', 'super_important', 'important_1', 'important_2'], item_priority).

Upvotes: 1

Related Questions