PostgreSQL
In this tutorial we will learn about ORDER BY and LIMIT in PostgreSQL.
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.
DESC
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;
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)
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";
We use the LIMIT keyword to manage the number of rows being fetched from the tables.
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.
row_count
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)
SELECT * FROM table_name LIMIT row_count OFFSET row_to_skip;
Where row_count and row_to_skip are non-negative integer value.
row_to_skip
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.
employee
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)
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)
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)