In Postgres, you can use ‘LIMIT’ clause to return only a certain number of rows by a query.
Syntax:
SELECT * from table1 LIMIT 10;
Above statement will return only 10 rows if there are more than 10 rows in the table.
If the table has fewer than 10 rows, all the rows will be returned.
Consider the employee table as shown below:
| id | first_name | last_name | department | salary |
| 101 | bryan | walker | sales | 50,000 |
| 102 | alex | carpenter | accounts | 55,000 |
| 103 | sam | white | sales | 60,000 |
| 104 | john | barry | sales | 54,000 |
| 105 | bill | wood | accounts | 65,000 |
| 106 | robert | hopkins | support | 70,000 |
| 107 | david | lee | support | 72,000 |
| 108 | jack | sears | support | 65,000 |
| 109 | tom | young | sales | 55,000 |
SELECT * FROM employee LIMIT 2;
Above sql query will return any 2 rows from the employee table. There is no control on which 2 rows will be returned. And, each time the query is executed, you might get a different data set. The output is unpredictable.
To obtain a predictable result set, you should use ORDER BY clause with the LIMIT clause.
Example: Below query will return the top 2 rows from the employee table sorted by salary in the descending order. Thus the 2 rows returned are the top 2 employees with highest salary amounts.
SELECT * FROM employee ORDER BY salary DESC LIMIT 2;
Also check out OFFSET Clause.
