EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ORDER BY
 

PostgreSQL ORDER BY

Updated May 3, 2023

PostgreSQL-ORDER-BY

 

 

Introduction to PostgreSQL ORDER BY

When we receive data from a table using a select query, it will return a row in an unspecified order. To sort this data using specified order, we need to use order by Clause in the select query to retrieve data from a table; we have to use ASC or DESC order to sort data in the specified order; this Clause is used to sort data in ascending and descending order in PostgreSQL. We can use one or more columns in order by Clause to sort data in ascending or descending order, but we ensure this column is in that table.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

This Clause specifies the sort order of data in a specific order.

SELECTcolumn-list (list of columns)
FROM table_name (name of table)
[WHERE condition]
[ORDER BY column1, column2, …, columnN (name of columns)] [ASC | DESC];
SELECT* (list of columns)
FROM table_name (name of table from we retrieving data)
[WHERE condition]
[ORDER BY column1, column2, …, columnN (name of columns)] [ASC | DESC];
Select expressions (column list)
From table_name (name of table)
[WHERE condition]
[ORDER BY expression (name of column) [ASC | DESC | Using operator] [NULLS FIRST | NULLS LAST];

Below is the parameter description of the above syntax are as follows:

  • Column list: List of columns name from which we have retrieved data.
  • Asterisk (*): All columns from the table that we have retrieving data.
  • The table name specifies the table name from which we retrieved data. There must be at least one name we have to mention in from Clause.
  • Where condition: Where specifies that the specific situation needs to be fulfilled to retrieve data from the table. Where the condition is optional in order by Clause.
  • ASC (Ascending): This clause is optional in PostgreSQL order by Clause. It will sort the result in ascending order.
  • DESC (Descending): This clause is optional in PostgreSQL order by Clause. It will sort the result in descending order.
  • Nulls first: It will sort all null values before non-nulls in PostgreSQL order by clause result set. This is an optional clause in PostgreSQL order by Clause.
  • Nulls last: It will sort all null values after non-nulls in PostgreSQL order by clause result set. This is an optional clause.

How ORDER BY Function works in PostgreSQL?

  • Typically without using the order by function in PostgreSQL, data retrieve in an unspecified order.
  • Our data comes in the specified order using the order by function in PostgreSQL. We have used the ASC function to sort data in ascending order.
  • Also, the DESC function is available in PostgreSQL to sort data in descending order.
  • We have used the column name in the order by Clause to sort data in our query’s specified order.
  • We have also used multiple column names in a query to fetch sorted data from the table.
  • This Clause is also used without specifying our query’s ASC and DESC functions. Suppose we have not provided ASC and DESC functions in order by Clause our result is sorted by ascending order.
  • The default sequence is in ascending order that will return the smallest value first. If suppose that two rows are equal in some situations, they compare using the expressions. If rows are the same by using the expression, then the result set’s output depends on the expression’s implementation.
  • It is not required to append ASC or DESC after any expressions in this Clause.
  • Null value sorts will rank higher than the other value sorts in this Clause.
  • In this Clause, two operators are used with the help of order by Clause using the keyword to retrieve the result set in ascending or descending order.

Examples

Below are some of the Examples.

Example #1

Create an employee table to describe order by Clause in PostgreSQL.

Code:

CREATE TABLE Employee ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), PRIMARY KEY (emp_id));

Output:

postgreSQL OUTPUT 1

Code:

INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'ABC', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (2, 'PQR', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (3, 'XYZ', 'Mumbai', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (4, 'BBS', 'Mumbai', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (5, 'RBS', 'Delhi', '1234567890');

Output:

insert employee

Example #2

Order By Clause to sort rows with a single column.

Code:

SELECT emp_id, emp_name, emp_address from employee order by emp_name ASC;

Output:

postgreSQL ORDERBY 3

Code:

SELECT emp_id, emp_name, emp_address from employee order by emp_name DESC;

Output:

sort rows with single column

Example #3

Order By Clause to sort rows with multiple columns.

Code:

SELECT emp_id, emp_name, emp_address from employee order by emp_name ASC, emp_id DESC;

Output:

postgreSQL ORDERBY 5

SELECT emp_id, emp_name, emp_address from employee order by emp_name DESC, emp_id ASC;

Output:

sort rows with multiple columns

Example #4

Order By Clause to sort rows with expressions.

Code:

SELECT emp_id, LENGTH(emp_address) emp_address, emp_name from employee order by length(emp_address) DESC;

Output:

postgreSQL ORDERBY 8

Code:

SELECT emp_id, LENGTH(emp_address) emp_address, emp_name from employee order by length(emp_address) ASC;

Output:

postgreSQL ORDERBY 9

Example #5

PostgreSQL order by Clause to sort rows using Clause.

Code:

SELECT emp_id, emp_name, emp_address from employee order by emp_id USING>;

Output:

postgreSQL ORDERBY 10

Code:

SELECT emp_id, emp_name, emp_address from employee order by emp_id USING<;

Output:

postgreSQL ORDERBY 11

Conclusion

PostgreSQL ORDER BY clause sorting is based on ascending or descending order. We can use single or multiple columns in the query to retrieve data in sorted order. This Clause will retrieve data in ascending order by default if we have not defined any sorting function in a query.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL ORDER BY” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. ORDER BY in Oracle
  2. PowerShell String Functions
  3. Hive String Functions
  4. Data Warehouse tools

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW