SQL query with arbitrary order

sql_query_with_arbitrary_order.md

Question

Fetch all the columns from table artists with id among (1, 2, 3) and with the arbitrary order (2, 3, 1)

Solutions

# Option 1
select * from artists join (values(1, 2), (2, 3), (3, 1)) as t (id, ordering) using (id) order by ordering;

# Option 2
select * from artists join unnest(array[2, 3, 1]) WITH ORDINALITY t(id, ordering) using (id) order by ordering;

Reasoning

Both options follow the following steps

1. Create a temporary table with columns (id, ordering). Try the following syntax to create and query the temp table

# Option 1
select * from (values(1, 3), (2, 2), (3, 1)) as t (id, ordering);
# Optoin 2
select * from unnest(array[3, 2, 1]) WITH ORDINALITY t(id, ordering);

2. Join the original table (artists) with the temp table on column id.
3. Use the ordering column to order the result.

References:

https://stackoverflow.com/questions/866465/order-by-the-in-value-list https://www.postgresql.org/docs/13/sql-select.html#SQL-ORDERBY https://www.postgresql.org/docs/13/queries-table-expressions.html https://www.postgresql.org/docs/13/functions-array.html