How to Simulate MySQL's ORDER BY FIELD() in PostgreSQL?

Simulating MySQL's ORDER BY FIELD() in PostgreSQL

As a MySQL user transitioning to PostgreSQL, you may encounter the limitation that the ORDER BY FIELD() syntax is not supported in the latter database. This raises the question of how to emulate its behavior in PostgreSQL.

Problem:

MySQL allows you to define a custom order using the ORDER BY FIELD() clause. It takes a value and a series of expected values, and the query results are sorted in the order of the provided values.

For example:

SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC

This query would prioritize rows with the codes 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', and 'USD' in that order, while further sorting the results in ascending order by the 'name' column.

Solution:

In PostgreSQL, you can achieve similar functionality using a combination of CASE statements and ordering by the resulting numeric values.

Here's how you can simulate the behavior of the ORDER BY FIELD() clause:

SELECT * FROM `currency_codes`
ORDER BY
CASE
    WHEN code='USD' THEN 1
    WHEN code='CAD' THEN 2
    WHEN code='AUD' THEN 3
    WHEN code='BBD' THEN 4
    WHEN code='EUR' THEN 5
    WHEN code='GBP' THEN 6
    ELSE 7
END,name;

In this query:

• The CASE statement assigns a numeric value (1-6) to each specified value of the 'code' column.
• Rows with the specified values are sorted in numerical order by the assigned values.
• If the 'code' column value does not match any of the specified values, it receives a default value of 7.
• The results are further sorted in ascending order by the 'name' column.

This technique allows you to prioritize rows based on a predefined order, providing a close approximation to the functionality of the MySQL ORDER BY FIELD() clause.

The above is the detailed content of How to Simulate MySQL's ORDER BY FIELD() in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!