I am trying to return the row with the maxium date for every person, how can I filter this

1. Initials, Date, Text
2. DD, 1/1/2022, 123
3. DD, 1/1/2021, 456
4. DD, 1/1/2020, 789
5. KT, 1/1/2020, abc
6. KT, 1/1/2022, def
7. KT, 1/2/2021, ghi

so the results appear like this?

1. Initials, Date, Text
2. DD, 1/1/2022, 123
3. KT, 1/1/2022, def

I've tried the MAX date and grouping by the initials, but I am trying to return the text from the entry with the maximum date.

Thank you

CodePudding user response：

There are several ways of doing it.

DDL (I used a temp table for testing purposes):

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp;

CREATE TABLE #tmp (
    initials varchar(10),
    date date,
    text varchar(10)
);

INSERT INTO #tmp
VALUES
    ('DD', '2022-01-01', '123'),
    ('DD', '2021-01-01', '456'),
    ('DD', '2020-01-01', '789'),
    ('KT', '2020-01-01', 'abc'),
    ('KT', '2022-01-01', '123'),
    ('KT', '2021-01-02', 'ghi');

Version 1 (Subquery with MAX): This version pulls unique initials and their MAX date, then links back to the original table on those initials and date to pull the text from the record.

SELECT t.initials, t.date, t.text
FROM #tmp AS t
INNER JOIN (
    SELECT initials, MAX(date) AS max_date
    FROM #tmp
    GROUP BY initials
) AS sub
    ON t.initials = sub.initials
    AND t.date = sub.max_date

Version 2 (Subquery with ROW_NUMBER): This version uses the window function ROW_NUMBER to number each row ordered by date descending. This will give the most recent date a row number of 1. Then pull the text from the records with row number = 1.

SELECT sub.initials, sub.date, sub.text
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY initials ORDER BY date DESC) AS row_num
    FROM #tmp
) AS sub
WHERE sub.row_num = 1

Since the text for both initials in your sample set on the max date are the same, I added initials and date to the final result set for clarification.

You can also use CTEs instead of subqueries, which I prefer, but I didn't want to complicate matters.