Limiting Query Results
Another SQL term you can add to your SELECT 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 whatcolumns FROM tablename LIMIT howmany SELECT whatcolumns FROM tablename LIMIT startingwhere, howmany SELECT * FROM tablename LIMIT 10 SELECT * FROM tablename LIMIT 10, 20
In the third example, only the initial 10 records from the query will be returned. In the fourth, 20 records will be returned, starting with the tenth.
You can use LIMIT with WHERE and/or ORDER BY, appending it to the end of your query.
SELECT * FROM invoices WHERE invoice_amount < 100.00 ORDER BY invoice_amount ASC LIMIT 10
Even though LIMIT does not reduce the strain of a query on the database (since it has to assemble every record, and then cut down the list), it will minimize overhead when it comes to the client or programming interface. As a rule, when writing queries, there is never any reason to return columns or rows that you will not need.
To limit the amount of data returned:
SELECT expense_amount, expense_description FROM expenses, expense_categories WHERE expenses.expense_category_id = expense_categories.expense_category_id AND expense_category = 'Sand Paper' ORDER BY expense_amount DESC LIMIT 2;
Figure 5.22. The very complicated query shown here distills the information stored in the accounting database into a supremely usable form.
This may look like a complex query, but it's just a good application of the information learned so far. First, I determine what columns to return, specifically naming the expense_amount and expense_description columns. Second, I list both tables to be used, since I'll be performing a join. Third, I establish my conditionals, which establish the join (expenses.expense_category_id = expense_categories.expense_category_id) and identify which category I want to use (expense_category = 'Sand Paper'). Finally, I sort the qualifying records so that the most expensive expenditures are listed first and then I limit it down to two records. If your database does not contain a sand paper expense category, alter this query using another example.