PostgreSQL - ORDER BY and LIMIT
PostgreSQL
In this tutorial we will learn about ORDER BY and LIMIT in PostgreSQL.
ORDER BY
We use ORDER BY to sort the rows in the result in ascending or descending order based on one or more columns.
By default, ORDER BY will sort the result in ascending order.
To sort the result in descending order we using the DESC keyword.
Sort in ascending order using single column
In the following example we are going to sort the result of the employee table in ASCENDING order based on firstName column.
SELECT * FROM employee ORDER BY "firstName";
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
e05 | Bob | Coder | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
e03 | Jane | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
e02 | John | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
e04 | Tin | Tin | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
e01 | Yusuf | Shakeel | 0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
(5 rows)
Note! We can add the ASC keyword to sort the result in ascending order. But this is optional because by default results get sorted in ascending order.
Following query will return the same result as shown above.
SELECT * FROM employee ORDER BY "firstName" ASC;
Sort in descending order using single column
We use the DESC keyword to sort the result in descending order.
In the following example we are sorting the result of the employee table in DESCENDING order by column name firstName.
SELECT * FROM employee ORDER BY "firstName" DESC;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
e01 | Yusuf | Shakeel | 0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
e04 | Tin | Tin | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
e02 | John | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
e03 | Jane | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
e05 | Bob | Coder | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(5 rows)
Sort using multiple columns
In order to sort the result using multiple columns we write comma separated column names with ASC or DESC keyword.
In the following example we are going to sort the result from the employee table by score in descending order and we are going to sort the result with the fisrtName and lastName in ascending order.
SELECT * FROM employee ORDER BY score DESC, "firstName" ASC, "lastName" ASC;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
e03 | Jane | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
e02 | John | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
e05 | Bob | Coder | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
e04 | Tin | Tin | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
e01 | Yusuf | Shakeel | 0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
(5 rows)
Following will give the same result as above.
SELECT * FROM employee ORDER BY score DESC, "firstName", "lastName";
LIMIT
We use the LIMIT keyword to manage the number of rows being fetched from the tables.
Limit rows
Following is the syntax to fetch certain number of rows from the table using LIMIT keyword.
SELECT * FROM table_name LIMIT row_count;
Where row_count is a non-negative integer value.
If we set LIMIT 0 then we will get zero rows when we run the SELECT query.
Alright, lets check an example using LIMIT in the SELECT query.
In the following example we are fetching 3 employees.
SELECT * FROM employee LIMIT 3;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
e01 | Yusuf | Shakeel | 0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
e04 | Tin | Tin | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
e05 | Bob | Coder | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(3 rows)
Limit rows with offset
SELECT * FROM table_name LIMIT row_count OFFSET row_to_skip;
Where row_count and row_to_skip are non-negative integer value.
First row_to_skip is considered then row_count tells the number of rows to return.
When using LIMIT we should also using ORDER BY because a table saves row in unordered fashion.
At the moment our employee table contains 5 rows. In the following example we are fetch the rows in multiple fetch each having max 3 rows.
So, for the first fetch we will use LIMIT 2 OFFSET 0.
LIMIT 2 OFFSET 0
When we have LIMIT 2 OFFSET 0 then we will be skipping 0 rows and then returning 2 rows.
In the following example we are going to order the employee table by employeeId and skip 0 rows and then return 2 rows.
SELECT * FROM employee ORDER BY "employeeId" LIMIT 2 OFFSET 0;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
e01 | Yusuf | Shakeel | 0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
e02 | John | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
(2 rows)
LIMIT 2 OFFSET 2
When we have LIMIT 2 OFFSET 2 then we will be skipping 2 rows and then returning 2 rows.
In the following example we are going to order the employee table by employeeId and skip 2 rows and then return 2 rows.
SELECT * FROM employee ORDER BY "employeeId" LIMIT 2 OFFSET 2;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
e03 | Jane | Doe | 9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
e04 | Tin | Tin | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
(2 rows)
LIMIT 2 OFFSET 4
When we have LIMIT 2 OFFSET 4 then we will be skipping 4 rows and then returning 2 rows.
In the following example we are going to order the employee table by employeeId and skip 4 rows and then return 2 rows.
Note! Since we have only 5 rows in the employee table and we are skipping 4 rows then we are only going to get 1 row from the SELECT query.
SELECT * FROM employee ORDER BY "employeeId" LIMIT 2 OFFSET 4;
employeeId | firstName | lastName | score | lastModifiedAt | createdAt | birthday | email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
e05 | Bob | Coder | 7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(1 row)
ADVERTISEMENT