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 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



Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...