How to Sort PostgreSQL Query Results by the Order of Values in an IN Clause?

Sort results by IN value list in PostgreSQL

In PostgreSQL, the ORDER BY clause usually acts on columns in a table. However, it can also be used to enforce ordering based on external references (such as IN clauses).

To ensure that the query results match the order of the list of IN values, you can use the VALUES() construct introduced in PostgreSQL 8.2.

Solution:

Create a temporary table or common table expression (CTE) to map IN values ​​to their desired sort order:

<code class="language-sql">WITH Ordering AS (
  VALUES
    (1,1),
    (3,2),
    (2,3),
    (4,4)
)</code>

Join the main table to this temporary table and alias it to x:

<code class="language-sql">SELECT c.*
FROM comments c
JOIN Ordering x ON c.id = x.id
ORDER BY x.ordering</code>

By referencing the ORDER BY column in the temporary table in the ordering clause, the results will be sorted in the order specified in the IN clause.

The above is the detailed content of How to Sort PostgreSQL Query Results by the Order of Values in an IN Clause?. For more information, please follow other related articles on the PHP Chinese website!