JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

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:

1.
Select the earliest invoice (Figure 5.21).

SELECT * FROM invoices ORDER BY invoice_date LIMIT 1\G

Figure 5.21. The LIMIT 1 clause will ensure that only one record is ever returned, saving me the hassle of viewing unnecessary rows of information.


To return the earliest anything, I must sort the data by a date, in ascending order. Then, to see just one invoice, I apply a LIMIT 1 to the query.

2.
Select the two most expensive sand paper expenditures (Figure 5.22).

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.

Tips

  • You'll almost always want to use a LIMIT with an ORDER BY clause. Without the ORDER BY, there's no guarantee as to the order in which the records are returned. Therefore, there would be no guarantee as to what records are affected by the LIMIT.

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



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©