The ORDER BY clause in PostgreSQL is used to sort the rows returned by a query based on one or more columns. By default, it sorts data in ascending order, but it can also sort in descending order.
- Can sort numeric, text and date values.
- Can be combined with clauses such as WHERE, LIMIT and OFFSET.
Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
Where:
- column1, column2, ... : The columns to retrieve.
- table_name: The name of the table.
- ORDER BY: Specifies the column(s) used for sorting.
- ASC (optional): Sorts data in ascending order (default).
- DESC (optional): Sorts data in descending order.
Examples of ORDER BY Clause
Firstly, create a student table and insert some records into it.
CREATE TABLE student (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
course VARCHAR(50),
age INT
);
INSERT INTO student (student_id, first_name, last_name, course, age)
VALUES
(101, 'John', 'Smith', 'Computer Science', 20),
(102, 'Emma', 'Johnson', 'Mathematics', 21),
(103, 'Michael', 'Brown', 'Physics', 22),
(104, 'Olivia', 'Davis', 'Chemistry', 20),
(105, 'William', 'Wilson', 'Biology', 23);
Output:
Example 1: Sort Data in Ascending Order
The following query sorts the students by age in ascending order.
Query:
SELECT *
FROM student
ORDER BY age;
Output:
Example 2: Sort Data in Descending Order
The following query sorts the students by age in descending order.
Query:
SELECT *
FROM student
ORDER BY age DESC;
Output:
Example 3: Sort by Multiple Columns
The following query first sorts the data by age in ascending order and then by first name in ascending order.
Query:
SELECT *
FROM student
ORDER BY age ASC, first_name ASC;
Output:
Example 4: Use ORDER BY with a WHERE Clause
The following query retrieves students older than 20 years and sorts them by age.
Query:
SELECT *
FROM student
WHERE age > 20
ORDER BY age;
Output:
Example 5: Use ORDER BY with LIMIT
The following query retrieves the two oldest students.
Query:
SELECT *
FROM student
ORDER BY age DESC
LIMIT 2;
Output:
