Grentley Grentley - 5 months ago 14
MySQL Question

SQL - Return the order of products purchased by adding new column

I'm working on another SQL query.
I have the following table.

PURCHASES

ID CUST_ID PROD_CODE PURCH_DATE
1 1 'WER' 01/12/2012
2 2 'RRE' 02/10/2005
3 3 'RRY' 02/11/2011
4 3 'TTB' 15/05/2007
5 3 'GGD' 20/06/2016
6 2 'SSD' 02/10/2011


I'm trying to add another column PURCH_COUNT that would display the purchase count for the CUST_ID based on PURCH_DATE.
If this is a first purchase it would return 1, if second then 2, and so on.
So the result I'm hoping is:

ID CUST_ID PROD_CODE PURCH_DATE PURCH_COUNT
1 1 'WER' 01/12/2012 1
2 2 'RRE' 02/10/2005 1
3 3 'RRY' 02/11/2011 2
4 3 'TTB' 15/05/2007 1
5 3 'GGD' 20/06/2016 3
6 2 'SSD' 02/10/2011 2


Thanks in advance!

vkp vkp
Answer

Use a correlated sub-query to get the counts per customer.

SELECT t.*,
 (SELECT 1+count(*)
   FROM table1
   WHERE t.cust_id = cust_id
     AND t.purch_date > purch_date) as purch_cnt
FROM table1 t
ORDER BY cust_id,purch_date

SQL Fiddle