Select Most Recent Distinct Customer Row

Question

I have a table that stores customer sales date like this:

|CustomerId|SalesDate |Product|Price| |1 |2015-01-01|A | 5.00| |1 |2015-02-01|A |10.00| |1 |2015-03-01|B | 7.00| |2 |2015-01-15|A | 9.00|

I'd like to select a single row for each customer who has purchased product A. If a customer has purchased product A more than once, I'd like to only see the most recent purchase.

This is the desired output:

|CustomerId|SalesDate |Product|Price| |1 |2015-02-01|A |10.00| |2 |2015-01-15|A | 9.00|

I've tried different versions of a group by/having queries like this:

SELECT CustomerId, SalesDate, Product, Price FROM Sales WHERE Product = 'A' GROUP BY CustomerId HAVING MAX(SalesDate);

Hogan · Accepted Answer · 2015-03-02 20:01:29Z

2

Just grab the max of sales date in the select statement.

select customerid, max(salesdate) as salesdate, 'A' from sales where product = 'A' group by customerid

with new requirements

select customerid, maxsalesdate, Product, Price from sales join (select customerid, max(salesdate) as maxsalesdate from sales where product = 'A' group by customerid) as sub on sub.customerid = sales.customerid and sub.maxsalesdate = sales.salesdate where product = 'A'

edited Mar 2, 2015 at 20:01

answered Mar 2, 2015 at 19:01

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

toconn Over a year ago

You need to add the product to the group by clause as well... Group By customerid, product

Hogan Over a year ago

@toconn - or make it constant (since it is)

DeadZone Over a year ago

This loses the price that they paid at that date.

Hogan Over a year ago

@DeadZone - True, that requirement was added after I answered the question

Hogan Over a year ago

@DeadZone - that better?

Gordon Linoff · Accepted Answer · 2015-03-02 19:54:12Z

Do this using a join and aggregation:

select s.* from sales s join (select customerid, max(salesdate) as salesdate from sales s where product = 'A' group by customerid ) cs on s.customerid = cs.customerid and s.salesdate = cs.salesdate where s.product = 'A';

The aggregation gets the maximum sales date for each customer. The join selects the row that matches that information.

I don't think you need the main where clause if it is in the inner join's sub-query. Is there ever a case where it would matter?
@Hogan . . . Yes. The user could have multiple sales on the most recent date for product A. The OP appears to only want information about the product A entry.

Collectives™ on Stack Overflow

Select Most Recent Distinct Customer Row

2 Answers 2

5 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Related