Skip to main content

ORDER BY clause in PostgreSQL

ORDER BY clause used to sort the result set return from the SELECT statement in ascending or descending order based on the specified criteria. Without using ORDER BY clause, when you query data from the table, PostgreSQL returns the rows in the order that they were inserted into the table. In many scenarios, we need to show the result set based on the specified column(s) ascending or descending, suppose I have an Employee table and want to see the employee's records based on their FirstName ( ascending or descending), in that type of cases ORDER BY clause help us to achieve the goal.

PostgreSQL - ORDER BY clause syntax:


SELECT
   Col_1,
   Col_2,
   Col_3,
   .....
FROM
   table_name
ORDER BY
   Col_1 ASC,
   Col_2 DESC;


Suppose we have the following table. You can create an Employee table using the below PostgreSQL statement.

CREATE TABLE public."Employee"
(
    "Id" bigserial NOT NULL,
    "FName" text COLLATE pg_catalog."default",
    "LName" text COLLATE pg_catalog."default",
    "UserName" text COLLATE pg_catalog."default",
    "Password" text COLLATE pg_catalog."default",
    "Contact" text COLLATE pg_catalog."default",
    "Salary" numeric NOT NULL DEFAULT 0.0,
    CONSTRAINT "PK_Test" PRIMARY KEY ("Id")
)


Insert some records on the above table("Employee")

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000');

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000');
               
INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name3','last3', 'name3@gmail.com', 'name@0123', '9388877787', '40000');

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name1','last1', 'name4@gmail.com', 'name@0123', '9384877787', '70000');

Illustrates of PostgreSQL ORDER BY clause


SELECT "Id", "FName", "LName", "UserName", "Password", "Contact", "Salary"
                FROM public."Employee" ORDER BY "FName" DESC;
               
We can sort the result set based on multiple columns

SELECT "Id", "FName", "LName", "UserName", "Password", "Contact", "Salary"
                FROM public."Employee" ORDER BY "FName" ASC, "Salary" DESC;


Result set accordingly