Limiting Query Results

Another SQL term you can add to your query statement is LIMIT. Unlike WHERE, which affects which records to return, or ORDER BY, which decides how those records are sorted, LIMIT states how many records to return. It is used like so:

 SELECT * FROM tablename LIMIT 10 SELECT * FROM tablename LIMIT 10, 20 

In the first example, only the initial 10 records from the query will be returned. In the second, 20 records will be returned, starting with the 11th. Like arrays in PHP, the indexes in databases begin at 0 when it comes to LIMITs, so 10 is the 11th record.

You can use LIMIT with WHERE and/or ORDER BY, appending it to the end of your query.

 SELECT * FROM users WHERE last_name =  'Simpson ORDER BY registration_date  DESC LIMIT 5 

Even though LIMIT does not reduce the strain of a query on the database (since it has to assemble every record and then truncate the list), it will minimize the amount of data to handle when it comes to the mysql client or your PHP scripts. As a rule, when writing queries, there is never any reason to return columns or rows you will not use.

To limit the amount of data returned

1.

Select the last five registered users (Figure 4.24). SELECT * FROM users ORDER BY registration_date DESC LIMIT 5; Figure 4.24. Using the LIMIT clause, I can return a more specific number of records. To return the latest of anything, I must sort the data by date, in descending order. Then, to see just the most recent five, I apply a LIMIT 5 to the query.

2.

Select the second person to register (Figure 4.25). SELECT * FROM users ORDER BY registration_date ASC LIMIT 1, 1; Figure 4.25. With SQL, you can even return records from the middle of a group, using the LIMIT x, y format. This may look strange, but it's just a good application of the information learned so far. First I order all of the records by registration_date ascending, so the first people to register would be returned first. Then I limit this group to start at 1 (which is the second row) and to return just one record.

Tips

• The LIMIT x, y clause is most frequently used when displaying multiple pages of query results where you would want to show the first 20 results, then the second 20, and so forth.

• In the next chapter, you'll learn one last clause to use with your SELECT statements, GROUP BY.

• The LIMIT term is not part of the SQL standard and is therefore (sadly) not available on all databases.

• The LIMIT clause can be used with most types of queries, not just SELECTs.