MySql Limit

SELECT c.first_name,c.last_name, o.order_total 
FROM customers c 
LEFT OUTER JOIN orders o ON c.id = o.cust_id
WHERE o.order_total > 100.0

I was once asked to write a query similar to this. The goal was to select all customers who had placed an order greater than 100.0.

I was then asked how I would select the person who had the second highest order amount.

Here’s how it’s done:

SELECT c.first_name,c.last_name, o.order_total 
LEFT OUTER JOIN orders o ON c.id = o.cust_id
WHERE o.order_total > 100.0
ORDER BY o.order_total DESC LIMIT(1,1)

Ordering by the order total column in descending order is the first step. The LIMIT part of the statement is what does the trick for getting us the second highest amount. The first number in the LIMIT is the offset into the result set. It’s zero indexed, so 1 will be row 2. The second number in the LIMIT statement is the number of rows to retrieve. We only want 1.

LIMIT is also used for paginating result sets. LIMIT(0,10), LIMIT(10,10), LIMIT(20,10), etc…