This chapter is from the book
This chapter is from the book
This chapter is from the book
Limiting Results
SELECT statements return all matched rows, possibly every row in the specified table. What if you want to return just the first row or a set number of rows? This is doable, but unfortunately, this is one of those situations where all SQL implementations are not created equal.
In Microsoft SQL Server you can use the TOP keyword to limit the top number of entries, as seen here:
Analysis ▾
The previous statement uses the SELECT TOP 5 statement to retrieve just the first five rows.
If you are using DB2, well, then you get to use SQL unique to that DBMS, like this:
Analysis ▾
FETCH FIRST 5 ROWS ONLY does exactly what it suggests.
If you are using Oracle, you need to count rows based on ROWNUM (a row number counter) like this:
If you are using MySQL, MariaDB, PostgreSQL, or SQLite, you can use the LIMIT clause, as follows:
Analysis ▾
The previous statement uses the SELECT statement to retrieve a single column. LIMIT 5 instructs the supported DBMSs to return no more than five rows. The output from this statement is shown in the following code.
To get the next five rows, specify both where to start and the number of rows to retrieve, like this:
Analysis ▾
LIMIT 5 OFFSET 5 instructs supported DBMSs to return five rows starting from row 5. The first number is the number of rows to retrieve, and the second is where to start. The output from this statement is shown in the following code:
So, LIMIT specifies the number of rows to return. LIMIT with an OFFSET specifies where to start from. In our example, there are only nine products in the Products table, so LIMIT 5 OFFSET 5 returned just four rows (as there was no fifth).