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

Simulating MySQL FIELD() in Postgresql

Converting from MySQL to PostgreSQL can bring unforeseen challenges. One such issue is simulating the ORDER BY FIELD() functionality, which is not directly supported in Postgresql.

In MySQL, the FIELD() function assigns ordinal positions to values in a specified list and sorts the results based on these positions. For instance, the following query in MySQL:

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

sorts the currency_codes table based on the order specified in the FIELD() list and then alphabetically by name.

To achieve similar behavior in Postgresql, you can use a CASE statement within the ORDER BY 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 priority values (1-6) to the codes in the specified order. Values not found in the list receive a default priority of 7. The results are then sorted in descending order of priority and ascending order of name.

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