SQL LIMIT

SQL > Advanced SQL > Limit

By default, all results that satisfy the conditions specified in the SQL statement are returned. However, this may not always be what we want, as sometimes we only want to retrieve a subset of records. In MySQL, this is accomplished using the LIMIT keyword. The syntax forLIMIT is as follows:

[SQL Statement 1]
LIMIT [N];

where [N] is the number of records to be returned. Please note that the ORDER BY clause is usually included in the SQL statement. Without the ORDER BY clause, the results we get would be dependent on what the database default is.

For example, we may wish to show the two highest sales amounts in Table Store_Information

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
San Francisco	300	Jan-08-1999
Boston	700	Jan-08-1999

we key in,

SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC
LIMIT 2;

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
Boston	700	Jan-08-1999

The SQL Server equivalent to LIMIT is TOP, which is covered next.