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

MySQLMySQLi Database

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

We have used an Online Compiler to run PostgreSQL.

PostgreSQL Example

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.

PostgreSQL Output

raja
Published on 20-Nov-2018 13:14:08
Advertisements