SQL Group By most recent date and sales value-CodePudding

I have the following sales table that displays the customer ID, their name, the order amount, and the order date.

ID	Name	Order	Date
1	A	25	11/10/2006
1	A	10	5/25/2010
1	A	10	6/18/2018
2	B	20	3/31/2008
2	B	15	11/15/2010
3	C	35	1/1/2019
3	C	20	4/12/2007
3	C	10	3/20/2010
3	C	5	10/19/2012
4	D	15	12/12/2013
4	D	15	2/18/2010
5	E	25	12/11/2006
6	F	10	5/1/2016

I am trying to group the data so that for each customer it would only show me their most recent order and the amount, as per below:

ID	Name	Order	Date
1	A	10	6/18/2018
2	B	15	11/15/2010
3	C	35	1/1/2019
4	D	15	12/12/2013
5	E	25	12/11/2006
6	F	10	5/1/2016

So far I've only been able to group by ID and Name, because adding the Order column would also group by that column as well.

SELECT 
  ID,
  Name,
  MAX(Date) 'Most recent date'
FROM Table
GROUP BY Customer, Customer

How can I also add the order amount for each Customer?

CodePudding user response：

SELECT ID, Name, Order, Date FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) AS sn
    FROM your_table_name
) A WHERE sn = 1;

CodePudding user response：

You could use a subqoery for max date SELECT ID, Name, MAX(Date) 'Most recent date' FROM Table GROUP BY Customer, Customer

select a.ID,   a.Name, b.max_date
from Table a 
inner join  (
    select name, max(Date) max_date
    from Table 
    group by name 
) b on a. name  = b.name and a.date = b.max_date

CodePudding user response：

You can use this query to get the expected result:

SELECT S.* 
FROM Sales S
CROSS APPLY
(
    SELECT ID, Max(Date) MaxDate 
    FROM Sales
    GROUP BY ID
)T 
WHERE S.ID = T.ID 
  AND S.Date = T.MaxDate 
ORDER BY S.ID