I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is returned. I need the SELECT statement to calculate a column that means "This is the customer's Nth purchase for the day." The data is sorted by customer name and date already, so when the customer name or date changes, I want the counter variable to reset to 1.
----------------------------------------------------------------
| Customer Name | Product | PurchaseDate | PurchaseNumberForDate |
----------------------------------------------------------------
| Customer A | ... | 2019-04-01 | 1 |
| Customer A | ... | 2019-04-02 | 1 |
| Customer A | ... | 2019-04-03 | 1 |
| Customer A | ... | 2019-04-03 | 2 |
| Customer A | ... | 2019-04-03 | 3 |
| Customer B | ... | 2019-04-03 | 1 |
| Customer B | ... | 2019-04-03 | 2 |
| Customer B | ... | 2019-04-04 | 1 |
----------------------------------------------------------------
I have tried using MySQL variables, but I cannot figure out how to reset the counter conditionally when the customer name or purchase date change. If I could get the PurchaseNumberForDate
correctly calculated, I will use this as a subquery with another query that will select WHERE PurchaseNumberForDate = 1
.
I have found plenty of examples using COUNT()
and @var := @var+1
, but I haven't found one based on multiple conditions. Is this possible with MySQL?
Best Answer
Variant without additional field:
fiddle