SQL Server How to find the oldest maximum in an elegant way?

7xllpg7q 于 2023-08-02 发布在其他

关注(0)|答案(4)|浏览(85)

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.

sql-server

来源：https://stackoverflow.com/questions/76755179/how-to-find-the-oldest-maximum-in-an-elegant-way

4条答案

按热度按时间

hgqdbh6s1#

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.

赞(0）回复(0）举报 2023-08-02

pgccezyw2#

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

赞(0）回复(0）举报 2023-08-02

hm2xizp93#

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

赞(0）回复(0）举报 2023-08-02

k97glaaz4#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server How to find the oldest maximum in an elegant way?

4条答案

相关问题

热门标签

最新问答