Home > Articles > Data > SQL

  • Print
  • + Share This
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 icon.jpg

SELECT TOP 5 prod_name
FROM Products;

Output icon.jpg

prod_name
-----------------
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Fish bean bag toy
Bird bean bag toy

Analysis icon.jpg

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 icon.jpg

SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;

Analysis icon.jpg

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 icon.jpg

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 icon.jpg

SELECT prod_name
FROM Products
LIMIT 5;

Analysis icon.jpg

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 icon.jpg

SELECT prod_name
FROMProducts
LIMIT 5 OFFSET 5;

Analysis icon.jpg

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 icon.jpg

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).

  • + Share This
  • 🔖 Save To Your Account