Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL: ORDER BY Clause

The SELECT statement returns rows in an unspecified order. Use the ORDER BY clause in the SELECT statement to get the data in ascending or descending order based on one or more columns.

Syntax
SELECT <column_1>, <column_2>,...<column_N> 
FROM <table_name>
ORDER BY <column_name>|<sort_expression> [ASC | DESC];

As per the above syntax, specify column_name or sort_expression that you want the result set to be sorted by after ORDER BY keywords. After specifying a column or expression, you can give optional ASC or DESC keywords for sorting in ascending or descending order. If you do not specify ASC or DESC, by default PostgreSQL will sort the result in ascending order.

PostgreSQL evaluates the SQL query in the following order: FROM, WHERE, GROUP BY, HAVING, ORDER BY, and then SELECT.

Let's use the following Employee table to demonstrate the ORDER BY clause.

Demo Table

The following query will fetch all the records from the Employee table and sorts the result in ascending order of the first_name values.

Example: ORDER BY
SELECT * FROM employee
ORDER BY first_name;

The above query will return the following result:

ORDER BY DESC

The DESC will return rows in the descending order of the first_name value, as shown below.

ORDER BY DESC

Sort by Multiple Columns

In PostgreSQL, the ORDER BY clause can include multiple columns in different sorting orders (ascending or descending). When you include multiple columns with the ORDER BY clause, it will sort the records based on the first column, and if any two or more records have the same value in the first ORDER BY column, it will sort them by the second ORDER BY column.

Example: Multi-Comlumn Sorting
SELECT * FROM employee
ORDER BY dept_id, first_name;

The above query will first sort the result by dept_id, and then the rows having the same dept_id will be sorted by the first_name. Remember, we have not included ASC or DESC, So it will sort the result in ascending order by default, as shown below.

ORDER BY Multiple Columns

Now let's run the same query with one column in ascending and another in descending order. The query will first sort the result by descending order of dept_id, and then the rows having the same dept_id will be sorted by ascending order of first_name.

Example: Multi-column Sorting
SELECT * FROM employee
ORDER BY dept_id DESC, first_name;
PostgreSQL ORDER BY Clause

Sort Group of Records

The following query sorts the group of records. It first group by dept_id, and then for each dept_id, it sort employees by descending order of dept_id.

Example: Sort Group Data
SELECT dept_id AS "Department Id", COUNT(emp_id) AS "No of Employees"
FROM employee
GROUP BY dept_id
ORDER BY dept_id DESC;
PostgreSQL ORDER BY Clause

Column alias can be used in the ORDER BY clause. The following query group by dept_id, where each dept_id gets many employees and sort by descending order of column alias emp_count.

Example:
SELECT dept_id, COUNT(emp_id) AS emp_count
FROM employee
GROUP BY dept_id
ORDER BY emp_count DESC;
PostgreSQL ORDER BY Clause
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.