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 and Microsoft Access you can use the TOP keyword to limit the top number of entries, as seen here:
Input 
SELECT TOP 5 prod_name FROM Products;
Output
prod_name ----------------- 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Fish bean bag toy Bird bean bag toy
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:
Input
SELECT prod_name FROM Products FETCH FIRST 5 ROWS ONLY;
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:
Input
SELECT prod_name FROM Products WHERE ROWNUM <=5;
If you are using MySQL, MariaDB, PostgreSQL, or SQLite, you can use the LIMIT clause, as follows:
Input
SELECT prod_name FROM Products LIMIT 5;
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:
Input
SELECT prod_name FROMProducts LIMIT 5 OFFSET 5;
Analysis
LIMIT 5 OFFSET 5 instructs supported DBMSs to return five rows starting from row 5. The first number is where to start, and the second is the number of rows to retrieve. The output from this statement is shown in the following code:
Output
prod_name ------------------- Rabbit bean bag toy Raggedy Ann King doll Queen doll
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).