only max(value) from union by several columns

user_15400759 0
Viewed 346

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
powerbi powerquery mysql sql
2 Answers

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;
user_11440350

Try select top 1 with order by in temporal tables and then query them with union

SELECT top 1[Group], ArticleNumber, max(TimeTrue) as Time into #tmp1
FROM PerformanceOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber
order by Time desc

SELECT top 1 [Group], ArticleNumber, max(StopTime) as Time into #tmp2
FROM StoppageOpc (NOLOCK) WHERE ([Group]='Pack2' OR [Group]='70521-030')
GROUP BY [Group], ArticleNumber
ORDER BY Time DESC
    
select * from #tmp1
union
select * from #tmp2

drop table #tmp1
drop table #tmp2
user_26125920
Related