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
Could you translate how to make this in Zend?
Pingback: Top Z result in Zend SQL - How-To Video
Pingback: Top Z result in Zend SQL | BlogoSfera
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 !
have clicked banner for you.
many code on stackoverflow don’t work, your code works well.
how to do this in mysql version 5.6.36 ?
Thank you