PostgreSQL LIMIT

The LIMIT clause does what it sounds like and limits the number of rows returned from your SELECT statement. For example, if I wanted to only see 5 rows from my employee table I might use a SQL statement like this:

SELECT * FROM employee LIMIT 5;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  1 | Joe        | Jones     | Sales       | 1034.23 | 2015-01-15 |
|  2 | Sally      | Smith     | Sales       | 1195.98 | 2015-01-15 |
|  3 | Lisa       | Teal      | Engineering | 2496.78 | 2019-03-10 |
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
+----+------------+-----------+-------------+---------+------------+

The SELECT statement will simply return the first 5 rows it scans. What if you wanted to see the 5 highest-paid employees? In that case, you would combine the LIMIT clause with an ORDER BY.

SELECT * FROM employee 
ORDER BY salary DESC LIMIT 5;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  9 | Mark       | Thompson  | [NULL]      | 3638.42 | 2008-11-22 |
|  3 | Lisa       | Teal      | Engineering | 2496.78 | 2019-03-10 |
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
|  8 | Sam        | Lopez     |             | 1658.00 | 2014-02-25 |
+----+------------+-----------+-------------+---------+------------+

Next, we can ask the database to tell us who is the second-highest-paid employee by combining LIMIT with an OFFSET value. This is also known as paging.

SELECT * FROM employee 
ORDER BY salary DESC LIMIT 1 OFFSET 1;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  3 | Lisa       | Teal      | Engineering | 2496.78 | 2019-03-10 |
+----+------------+-----------+-------------+---------+------------+

In the above example, we are aking the database to first sort the rows descending by salary, then skip the 1st row and return the next 1 row. ORDER BY is a simple concept, but one you will use often. Generally, PostgreSQL will return rows in the order they were inserted into a table, but there is no guarantee of that order unless you specify the ORDER BY clause.

Next: PostgreSQL GROUP BY