only max(value) from union by several columns

I want to retrieve one result for each [Group] by the highest Time.

Result of current code:

enter image description here

SELECT [Group], ArticleNumber, max(TimeTrue) as Time
FROM PerformanceOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber

UNION

SELECT [Group], ArticleNumber, max(StopTime) as Time
FROM StoppageOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber
ORDER BY Time DESC

The result should be only two records (csv):

enter image description here

Group,ArticleNumber,Time
70521-030,,2021-03-15 13:50:15
Pack2,183026,2021-03-15 13:47:39

Answer

Hmmm . . . you would seem to want to union all before aggregating:

SELECT [Group], ArticleNumber, max(Time) as Time
FROM ((SELECT [Group], ArticleNumber, TimeTrue as Time
       FROM PerformanceOpc 
       WHERE [Group] IN ('Pack2', '70521-030')
      ) UNION ALL
      (SELECT [Group], ArticleNumber, StopTime as Time
       FROM StoppageOpc
       WHERE [Group] IN ('Pack2', '70521-030')
      )
     ) g
GROUP BY [Group], ArticleNumber;

This returns one row per group and article, which seems to be what your query is doing.

If you really want only one row per group, then you want ROW_NUMBER() and not aggregation:

SELECT g.*
FROM (SELECT g.*, ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY time DESC) as seqnum
      FROM ((SELECT [Group], ArticleNumber, TimeTrue as Time
             FROM PerformanceOpc 
             WHERE [Group] IN ('Pack2', '70521-030')
            ) UNION ALL
            (SELECT [Group], ArticleNumber, StopTime as Time
             FROM StoppageOpc
             WHERE [Group] IN ('Pack2', '70521-030')
            )
           ) g
      ) g
WHERE seqnum = 1;

Leave a Reply

Your email address will not be published. Required fields are marked *