Simulating MySQL's ORDER BY FIELD() in PostgreSQL?

The following is the process to simulate MySQL’s ORDER BY FIELD() in PostgreSQL.

We have used an Online Compiler to run PostgreSQL.

Let us now see what we did above to get the output.

Firstly, we created a table.

create table PostgreOrderIdDemo (
   countryName varchar(20)
);

Inserted records with the help of INSERT command.

insert into PostgreOrderIdDemo values('IND');
insert into PostgreOrderIdDemo values('US');
insert into PostgreOrderIdDemo values('UK');

The following is the syntax in PostgreSQL.

SELECT * FROM yourTableName
ORDER BY
CASE
WHEN columnName='IND' THEN 1
WHEN columnName='US' THEN 2
WHEN columnName='UK' THEN 3
ELSE 0
END,columnName;

Example

Let us now use the above syntax to get the output.

SELECT * FROM PostgreOrderIdDemo
ORDER BY
CASE
WHEN countryName='IND' THEN 1
WHEN countryName='US' THEN 2
WHEN countryName='UK' THEN 3
ELSE 0
END,countryName;

Output

The following is the output.

Arjun Thakur

Updated on: 26-Jun-2020

277 Views

Kickstart Your Career

Get certified by completing the course

Get Started