sql limit

Sometimes we may not want to retrieve all the records that satsify the critera specified in WHERE or HAVING clauses.

In MySQL, this is accomplished using the LIMIT keyword. The syntax for LIMIT is as follows:

[SQL Statement 1]
LIMIT [N]

where [N] is the number of records to be returned. Please note that the 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	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, Date
FROM Store_Information
ORDER BY Sales DESC
LIMIT 2;

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
Boston	$700	Jan-08-1999