Sample Header Ad - 728x90

Ordering by IN clause order

0 votes
2 answers
45 views
Suppose I want the order of output records match the order in the IN clause
SELECT * FROM (
SELECT 1 AS id, 'one' AS text FROM DUAL
UNION ALL
SELECT 2 AS id, 'two' AS text FROM DUAL
) t WHERE t.id IN (2,1)
Expected: |id|text| |-|-| |2|two| |1|one| Actual: |id|text| |-|-| |1|one| |2|two| Obviously, I can't hardcode it like this (GPT's suggestion). Ids and their order are dynamic
SELECT * FROM (
  SELECT 1 AS id, 'one' AS text FROM DUAL
  UNION ALL
  SELECT 2 AS id, 'two' AS text FROM DUAL
) t 
WHERE t.id IN (2, 1)
ORDER BY CASE t.id
  WHEN 2 THEN 1
  WHEN 1 THEN 2
END;
How do I do it? Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production (bonus points if it works for PostgreSQL as well)
Asked by Sergey Zolotarev (243 rep)
Nov 28, 2024, 07:44 AM
Last activity: Nov 28, 2024, 01:44 PM