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
Last activity: Nov 28, 2024, 01:44 PM