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


Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str