Get the records by a unique key with max value from 2 columns

Question

Imagine that I have Table A:

Value1	Value2	Value3	Date1	Date2
1	2	1	2022/02/01	1900/02/01
1	2	2	2004/02/01	1992/02/01
2	2	2	2022/02/01	2001/07/01
3	3	1	2021/02/01	1990/02/01
3	3	2	2021/02/01	1980/02/01
3	3	3	2005/02/01	2022/02/01

I want to have a query that returns the records for each pair (Value1, Value2) with max Date1 in case of same Date1, the ones with max Date2.

For this example, I want to get the following results:

Value1	Value2	Value3	Date1	Date2
1	2	1	2022/02/01	1900/02/01
2	2	2	2022/02/01	2001/07/01
3	3	1	2021/02/01	1990/02/01

I'm trying to use the SELECT - OVER clause in the following query:

SELECT 
      Value1,
      Value2,
      --Value3 OVER (PARTITION BY Value1, Value2 ORDER BY Date1 DESC, Date2 DESC) AS Value3,
      MAX(Date1) OVER (PARTITION BY Value1, Value2) AS FinalDate1,
      MAX(Date2) OVER (PARTITION BY Value1, Value2) AS FinalDate2
FROM A

but I'm getting the following error:

Msg 8120, Level 16, State 1, Server b784427b284a, Line 21
Column 'A.Date1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Also, not sure about how to handle the Value3 column since I just want to get it's value without any aggregate function or similar.

Does anyone have an idea on how I can do this?

That query won't generate that error, there is no non-windowed aggregates in your query, and thus there is no need for a `GROUP BY` clause. — Thom A, Dec 29 '22 at 11:17

score 0 · Accepted Answer · answered Dec 29 '22 at 11:35

You can use ROW_NUMBER to partition and ordering the data like what you want. And you can use CTE to filter the ROW_NUMBER

Data prepare:

DECLARE @vTable TABLE (
    Value1      INT,
    Value2      INT,
    Value3      INT,
    Date1       DATE,
    Date2       DATE
)

INSERT INTO @vTable
VALUES
    (1, 2, 1, '2022-02-01', '1900-02-01'),
    (1, 2, 2, '2004-02-01', '1992-02-01'),
    (2, 2, 2, '2022-02-01', '2001-07-01'),
    (3, 3, 1, '2021-02-01', '1900-02-01'),
    (3, 3, 2, '2021-02-01', '1980-02-01'),
    (3, 3, 3, '2005-02-01', '2022-02-01');

The query script:

;WITH CTE AS (
    SELECT
          RowNumber     = ROW_NUMBER() OVER (PARTITION BY Value1, Value2 ORDER BY Date1 DESC)
        , Value1
        , Value2
        , Value3
        , Date1
        , Date2
    FROM
        @vTable
)
SELECT
    *
FROM
    CTE
WHERE
    RowNumber = 1

The result:

RowNumber	Value1	Value2	Value3	Date1	Date2
1	1	2	1	2022-02-01	1900-02-01
1	2	2	2	2022-02-01	2001-07-01
1	3	3	1	2021-02-01	1900-02-01

Honestly, we don't need the 300th+ duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group)... — Thom A, Dec 29 '22 at 11:35
I'm sorry about that and I didn't know. Thanks for the cultural information — Karcan, Dec 29 '22 at 11:45

Get the records by a unique key with max value from 2 columns

1 Answers1