0

I have the next table:

Table SALES

Id CustomerId Date NumItems
1 4 2023-01-14 2
2 4 2023-01-16 6
3 4 2023-01-10 9

I'm grouping the rows by CustomerId and I need to get the one whose date is the most recent:

SELECT
    CustomerId,
    MAX(Date),
    NumItems  <--- (?)
FROM SALES 
GROUP BY CustomerId

But I also need to get number of items from the same row. How can I complement my query to get that column?

If the most recent registry is the second one (2023-01-16), I have to get the number of items from that row as well, which would be 6.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Daym
  • 3
  • 2
  • This can be done using the ROW_NUMBER() Windowing function. See the first answer to [this question](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). – T N Feb 06 '23 at 23:00
  • 1
    Please don't tag conflicting products, just tag the one you're actually using. – Thom A Feb 06 '23 at 23:08
  • 1
    Now we went from two DBMS tags (sql-server and postgresql) to none. One or the other would be preferred. – T N Feb 06 '23 at 23:37

1 Answers1

0

You can do it as follows :

with cte as (
  SELECT
    CustomerId,
    MAX(Date) as Date
  FROM SALES 
  GROUP BY CustomerId
)
select c.*, s.NumItems
from cte c
inner join SALES s on s.CustomerId = c.CustomerId and c.Date = s.Date

a subquery to get the recent date and then using inner join to get NumItems

SelVazi
  • 10,028
  • 2
  • 13
  • 29