1

I'm new to Redshift and looking for a way to specify the order I want the results in, the accepted answer here is exactly what I want, but the FIELD function does not exist in Redshift. Is there a way to accomplish the same result in Redshift?

Current Query looks like this:

SELECT DISTINCT audit_type, audit_category FROM "schema"."table"

ORDER BY audit_type,    
CASE WHEN audit_category = 'string_1' THEN 1 
  WHEN audit_category = 'string_2' THEN 2 
  WHEN audit_category = 'string_3' THEN 3 
  WHEN audit_category = 'string_4' THEN 4 
  WHEN audit_category = 'string_5' THEN 5 
  WHEN audit_category = 'string_6' THEN 6 
  WHEN audit_category = 'string_7' THEN 7 
ELSE 8 END
Will Ross
  • 25
  • 1
  • 8

1 Answers1

3

you can just use CASE statement to achieve the same:

ORDER BY CASE WHEN "name"='core' THEN 1 ELSE 2 END, priority

you can extend the list with another option besides core by adding a new WHEN / THEN part before ELSE, so what you ultimately do is map a list of possible name options to integers that are used for sorting the rows

AlexYes
  • 4,088
  • 2
  • 15
  • 23
  • CASE WHEN audit_category = 'string_1' THEN 1 WHEN audit_category = 'string_2' THEN 2 WHEN audit_category = 'string_3' THEN 3 WHEN audit_category = 'string_4' THEN 4 WHEN audit_category = 'string_5' THEN 5 WHEN audit_category = 'string_6' THEN 6 WHEN audit_category = 'string_7' THEN 7 ELSE 8 END I tried this, but it seems to still randomly order them? – Will Ross Dec 09 '17 at 02:41
  • @WillRoss it should sort, please post the entire query in the original question – AlexYes Dec 09 '17 at 10:47
  • Added current query into original question – Will Ross Dec 10 '17 at 01:02
  • @WillRoss in your example CASE statement should go first in the `order by` list if you want to prioritize ordering by that expression, and then `audit_type` – AlexYes Dec 10 '17 at 16:41
  • @WillRoss does this answer the question? – AlexYes Dec 13 '17 at 00:24
  • This solution works, I found the issue after some troubleshooting was in the strings being case sensitive. Not all of the categories followed the same case usage. – Will Ross Dec 13 '17 at 15:40