Postgresql Order by based on specific values

Question

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

SQLpro · Accepted Answer · 2021-06-11 05:10:02Z

4

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...

answered Jun 11, 2021 at 5:10

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HOOOPLA Over a year ago

That unfortunately didn't really change the order. I.e important shows last before anything.

AdamKG · Accepted Answer · 2021-06-10 18:36:47Z

1

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).

answered Jun 10, 2021 at 18:36

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

1 Comment

HOOOPLA Over a year ago

I am running into syntax issues. : syntax error at or near "[" LINE 45: ORDER BY array_position([ I added ARRAY before that and now I am getting HINT: No function matches the given name and argument types. You might need to add explicit type casts. This is what I ended up with

ORDER BY array_position(ARRAY['important', 'super_important', 'important_1', 'important_2'], item_priority) , col1, col2, col3

Collectives™ on Stack Overflow

Postgresql Order by based on specific values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related