WDS 9 - The Limit function
When running queries in the database sometimes many recordsets can be returned. Using the “SELECT” function with “WHERE” in the statement we can filter out more of the recordsets that don’t met our criteria. If we are dealing with a large database with many records this still might not filter our results down to a scalable level. Using “LIMIT” as part of our select statement can help us filter down to a certain number of records. If we sort our query by descending using the “ORDER BY [row_name] DESC” then use “LIMIT [number]” to only show the top records according to that row. This can be used to show the top 10 or top 5 of sales figures or anything else that has a numeric value. A query can be limited even if the values are not numerical. Any query can be limited by any amount.
An example:
Let’s say we have the table below in our database. If this database was much larger, let’s say nationwide sales by city we might not want to see every single one. We might not even want to filter it by sales over a certain amount if many of them are close in value. If we wanted to see the top 2 out of these records (for something larger we might want to see the top 10) we could sort it and limit to 2 records show.
Store_Information
| store_name | Sales | Date |
|---|---|---|
| Los Angles | $1500 | Jan-05-2007 |
| San Diego | $250 | Jan-07-2007 |
| San Francisco | $300 | Jan-08-2007 |
| Boston | $700 | Jan-08-2007 |
Funtion:
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
LIMIT 2;
This will return the following results
| store_name | Sales | Date |
|---|---|---|
| Los Angles | $1500 | Jan-05-2007 |
| Boston | $700 | Jan-08-2007 |