Get the record with the most recent date. SQL

user_12507364 0
Viewed 173
ID Date Value
715 2022-03-20 183.74
715 2022-03-13 86.45
715 2022-03-06 -10.84
715 2022-02-27 291.87
715 2022-02-20 194.58
715 2022-02-13 97.29
715 2022-02-06 0.00

Is there a way to get the ID,and value for the last day of each month provided.

Output

ID Date Value
715: :2022-03-20: 183.74:
715: :2022-02-27: 291.87:

Thanks, I have already tried

SELECT T.*
FROM (
  SELECT ID, value, date,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date ASC) AS rn
  FROM table
) t

But doesn't quite do it.

Thanks

sql-server sql
1 Answers

Adjust your current query to sort row_number descending and add a partition on the month of your date. Then, add a where clause to pull rn = 1:

SELECT T.*
FROM (
  SELECT ID, value, date,
    ROW_NUMBER() OVER (PARTITION BY ID, month(Date) ORDER BY Date desc) AS rn
  FROM table
) t
where t.rn = 1

If you need to expand this to multiple years, you can inlcude a partition on the year as well:

SELECT T.*
FROM (
  SELECT ID, value, date,
    ROW_NUMBER() OVER (PARTITION BY ID, month(Date), year(Date) ORDER BY Date desc) AS rn
  FROM table
) t
where t.rn = 1
user_125712410
Related