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
case ORDER BY clause helps 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