Get top N rows of each group in MySQL

For this example I have created the tables customers and orders. The table customers contains all the existing users and their ids:

customers

customer_id name
1 Jimmy
2 John
3 Lucas

And the table orders contains all the orders associated to those customers:

orders

order_ref customer_id date
ref#001 1 2012-09-01 00:00:00
ref#002 1 2012-09-02 00:00:00
ref#003 1 2012-09-03 00:00:00
ref#004 1 2012-09-04 00:00:00
ref#005 2 2012-09-05 00:00:00
ref#006 2 2012-09-06 00:00:00
ref#007 2 2012-09-07 00:00:00
ref#008 2 2012-09-08 00:00:00
ref#009 3 2012-09-09 00:00:00
ref#010 3 2012-09-10 00:00:00
ref#011 3 2012-09-11 00:00:00
ref#012 3 2012-09-12 00:00:00

Now imagine you need a query to get the last two orders of each customer. You can get it making a query for each customer, using something like this:

SELECT 
  * 
FROM 
  orders 
WHERE 
  customer_id=[the_customer_id] 
ORDER BY 
  date DESC 
LIMIT 2

But if there are hundreds of customers that means hundreds of queries, therefore it’s a bad idea. There is a better way to do this, we just need to use sub-queries and variables.

SELECT 
    c.customer_id,
    c.name,
    sub_table.order_ref,   
    sub_table.date    
FROM
    (SELECT 
            customer_id,
            order_ref,
            date,
            @rn:=CASE
                WHEN @var_customer_id = customer_id THEN @rn + 1
                ELSE 1
            END AS rn,
            @var_customer_id:=customer_id
    FROM
        (SELECT @var_customer_id:=NULL, @rn:=NULL) vars, orders
        WHERE
        customer_id IN (SELECT customer_id FROM customers)
    ORDER BY customer_id , date DESC) as sub_table
    INNER JOIN customers c 
        on c.customer_id=sub_table.customer_id
WHERE
    rn <= 2
ORDER BY customer_id , date DESC

Voilà! we got what we were looking for.

customer_id name order_ref date
1 Jimmy ref#004 2012-09-04 00:00:00
1 Jimmy ref#003 2012-09-03 00:00:00
2 John ref#008 2012-09-08 00:00:00
2 John ref#007 2012-09-07 00:00:00
3 Lucas ref#0012 2012-09-12 00:00:00
3 Lucas ref#0011 2012-09-11 00:00:00

Comments 6

  1. Pingback: Top Z result in Zend SQL - How-To Video

  2. Pingback: Top Z result in Zend SQL | BlogoSfera

  3. Hi, thanks for this post, it has helped me into solving some issue getting top N for some datas, and discovering user vars in MySQL !

Leave a Reply to Elisabeth Cancel reply

Your email address will not be published. Required fields are marked *