How to limit query results using LIMIT, TOP, and ROWNUM??
Syntax
When you have a very large set of data and running a SELECT query would take up a long time. To skip the wait, you can use a LIMIT operator in the SELECT query to set the maximum number of records that the query would return.
Each database has its own syntax to limit query results. Here is the generic syntax for MySQL, PostgreSQL, SQLite:
SELECT column_name
FROM table_name
LIMIT n;
So with the LIMIT operator included above, when you execute the query, it will return up to n first records from the results set.
In MS SQL Server, the database system doesn’t use LIMIT but a SELECT TOP clause:
SELECT TOP n column_name
FROM table_name;
You can specify the exact number of records to be returned, or the percentage of the entire results set to be returned.
In Oracle, it uses a different method called ROWNUM:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= n;
Example
Here we have the table dept_manager:
| emp_id | dept_id | from_date | to_date |
|---|---|---|---|
| 110022 | d001 | 1985-01-01 | 1991-10-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 |
| 110085 | d002 | 1985-01-01 | 1989-12-17 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 |
| 110183 | d003 | 1985-01-01 | 1992-03-21 |
| 110228 | d003 | 1992-03-21 | 9999-01-01 |
| 110303 | d004 | 1985-01-01 | 1988-09-09 |
| 110344 | d004 | 1988-09-09 | 1992-08-02 |
| 110386 | d004 | 1992-08-02 | 1996-08-30 |
| 110420 | d004 | 1996-08-30 | 9999-01-01 |
| 110511 | d005 | 1985-01-01 | 1992-04-25 |
Now select the first 4 rows from it.
In MySQL/PostgreSQL:
SELECT * FROM dept_manager LIMIT 4;
In MS SQL Server:
SELECT TOP 4 * FROM dept_manager;
In Oracle:
SELECT * FROM dept_manager WHERE ROWNUM <= 4;
The results will all be:
| emp_id | dept_id | from_date | to_date |
|---|---|---|---|
| 110022 | d001 | 1985-01-01 | 1991-10-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 |
| 110085 | d002 | 1985-01-01 | 1989-12-17 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 |
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
