How Can I Order SQL Results to Match the Order of Values in an IN List?

Arrange the results in order of the IN list

In SQL, a common scenario is to use the IN operator to retrieve data based on a specified set of values. In this case, you may want to order the results in the order in the IN list.

In versions of PostgreSQL prior to 8.4, one way to achieve this sorting was to use a correlated subquery. However, for 8.2 and above, a more efficient approach is available:

VALUES function

PostgreSQL introduced the VALUES function in version 8.2, which allows the creation of in-memory tables. This function can be used to build a temporary table containing the desired order of values:

<code class="language-sql">select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering</code>

In this query:

• The VALUES function creates a temporary table named "x" that contains two columns: "id" and "ordering".
• The "id" column contains the value specified in the IN clause.
• The "ordering" column assigns a numerical order to each value, ensuring the desired arrangement of the results.
• Temp table "x" is joined with the "comments" table based on the common "id" column.
• Finally, the results are sorted by the "ordering" column, generating the desired order of comments.

The above is the detailed content of How Can I Order SQL Results to Match the Order of Values in an IN List?. For more information, please follow other related articles on the PHP Chinese website!