MySQL Conditional Counter Based Other Columns

greatest-n-per-groupmariadbmariadb-10.1MySQLmysql-5.7

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

SELECT t.*, 
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END PurchaseNumberForDate,
@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) groupname
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate 

Variant without additional field:

SELECT t.*, 
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END + CONCAT(CHAR(0), (@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate))) PurchaseNumberForDate
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate;

fiddle