How to find the oldest maximum in an elegant way?

Question

I have a table:

Insert Date	Value1	Value2	Group
2023-01-01	123	135	a
2023-02-01	234	246	a
2023-01-02	456	468	b
2023-02-02	345	357	b

I would like to find for each group when it had the highest value and then from these dates choose the oldest.
for the above data: group a has the maximum in February, group b has the maximum in January, so the final result I expect is 2023-01-02.
currently I do it like this:

SELECT MIN([Insert Date])
FROM (
  SELECT [Insert Date], [RowNo] = ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY [Value1] DESC, [Insert Date] DESC) FROM table WITH(NOLOCK)
  UNION
  SELECT [Insert Date], [RowNo] = ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY [Value2] DESC, [Insert Date] DESC) FROM table WITH(NOLOCK)
) AS src
WHERE [RowNo] = 1

But I don't think this is the best available way.

This looks like a [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) but you have `MIN` in the outer `SELECT` instead. — Thom A, Jul 24 '23 at 13:57
What is your *good* reason for using `NOLOCK` against your table? You do understand what it does, right? [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — Thom A, Jul 24 '23 at 13:57
I would recommend changing how your data are stored. Better to have just one value column instead of n-nbr of value columns. Make a tall table. — Isolated, Jul 24 '23 at 13:59
So, you want to look at all rows that have a maximum value in one of the columns per group? So with three rows for a group with (Value1/Value2) = (100,2000), (200,1000), (200,2000), you would consider all three rows, for each has either a maximum for Value1 (200) or a maximum for Value2 (2000)? Yes? — Thorsten Kettner, Jul 24 '23 at 14:25
If the aforementioned is what you really want, then your query is correct. But I'd suggest to replace `UNION` with `UNION ALL`. Don't force the DBMS to remove duplicates. Let it decide itself. — Thorsten Kettner, Jul 24 '23 at 14:33

score 0 · Answer 1 · edited Jul 24 '23 at 15:21

0

Looks like you could simply use GREATEST inside the row-number's ordering.

SELECT
  MIN(t.[Insert Date])
FROM (
    SELECT *,
      RowNo = ROW_NUMBER() OVER (PARTITION BY t.[Group]
                ORDER BY GREATEST(t.Value1, t.Value2) DESC)
    FROM YourTable t
) t
WHERE t.RowNo = 1;

On older versions you don't have GREATEST. You can instead do

RowNo = ROW_NUMBER() OVER (PARTITION BY t.[Group]
          ORDER BY IIF(t.Value1 > t.Value2, t.Value1, t.Value2) DESC)

db<>fiddle

edited Jul 24 '23 at 15:21

siggemannen

3,884
2
6
24

answered Jul 24 '23 at 14:02

Charlieface

52,284
6
19
43

1. unfortunately I cannot use GREATEST 2. it looks good, unfortunately I have a total of 5 columns and would need to use `CASE`. – Marcin Jul 26 '23 at 06:17
Another option in that case is to unpivot and `MAX` inside an `APPLY` https://dbfiddle.uk/CEqM04T5 – Charlieface Jul 26 '23 at 11:15

xQbert · Answer 2 · 2023-07-24T14:24:18.863

You could use cross apply: If we assume Insert date is unique per group... I'm not sure how you define "elegant" easy to read? performance?

With mtable AS (SELECT
'2023-01-01' as InsertDate, 123 value1, 135 value2, 'a' as [group] UNION ALL
SELECT '2023-02-01',    234,    246,    'a' UNION ALL
SELECT '2023-01-02',    456,    468,    'b' UNION ALL
SELECT '2023-02-02',    345,    357,    'b')
SELECT C.*
FROM mtable A
Cross apply (Select Top 1 * 
             FROM  mtable B
             WHERE  A.[Group] = B.[Group]
              order by Value1 desc) C
     WHERE  A.InsertDate = C.InsertDate

DBFiddle Demo

Returning:

+------------+--------+--------+-------+
| InsertDate | value1 | value2 | group |
+------------+--------+--------+-------+
| 2023-02-01 |    234 |    246 | a     |
| 2023-01-02 |    456 |    468 | b     |
+------------+--------+--------+-------+

You'd have to compare explain plans to see which is optimal given your system setup.

score 0 · Answer 3 · answered Jul 24 '23 at 14:27

This is an other approach to do it using CTEs :

with grt as (
  select *, GREATEST([Value1], [Value2]) as GREATEST
  from mytable
),
max_grt as (
  select [Group], max(GREATEST) as max_GREATEST
  from grt
  group by [Group]
),
cte as (
  select g.*
  from max_grt as mg
  inner join grt as g on g.GREATEST = mg.max_GREATEST
)
select min([Insert Date])
from cte

Demo here

score 0 · Answer 4 · answered Jul 24 '23 at 14:29

You can use a simple ORDER BY to solve it i think:

SELECT  TOP 1 [insert date]
FROM    (
    VALUES  (N'2023-01-01', 123, 135, N'a')
    ,   (N'2023-02-01', 234, 246, N'a')
    ,   (N'2023-01-02', 456, 468, N'b')
    ,   (N'2023-02-02', 345, 357, N'b')
) t ([Insert Date],Value1,Value2,[Group])
ORDER BY
  ROW_NUMBER() OVER(PARTITION BY [group] ORDER BY CASE WHEN value1 > value2 THEN value1 ELSE value2 END DESC)
, t.[Insert Date]

This first sorts by every group's largest value1 or value2, and then takes the earliest date of those values. If you have duplicate max valueXs, you can add a second tie breaker to the ROW_NUMBER call

`ORDER BY ROW_NUMBER()` while elegant still requires two sorts. — Charlieface, Jul 24 '23 at 15:18

How to find the oldest maximum in an elegant way?

4 Answers4