Group by a few columns including MAX() to limit rows but display columns not in group by

I’m outputting sp_whoisactive to a log table every 5 minutes and want to aggregate columns such as elapsed time or cpu. My best chance for unique record is session_id, login_time, login_name and I want the MAX(collection_time) for each unique record.

My question is how do you do this in one query?

I tried all kind of variations and could never get the desired output. I ended up doing 2 steps 1) the group by to a ##Temp table and 2) then joining the ##temp to the log table to get the all the columns I wanted in the report.

Step 1 query:

    enter code hereIF(OBJECT_ID('tempdb..##TEMP') IS NOT NULL) BEGIN DROP TABLE ##TEMP END
        SELECT *
            INTO ##TEMP
            FROM (
select distinct Session_id, login_name, login_time, max(collection_time) as MaxCollection
from [MyDB].[DBA].[WhoIsActive]
group by login_name,  session_id, login_time
                ) AS X

Step 2 query:

DECLARE @yesterday DATETIME, @today DATETIME
SET @yesterday = DATEADD(hh,12,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
SET @today = DATEADD(hh,11,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))

SELECT 
       tt.[session_id]
      ,tt.[tasks]
      ,tt.[login_time]
      ,tt.[login_name]
      ,tt.[start_time]
      ,tt.[collection_time]      
      ,tt.[dd hh:mm:ss.mss] as ElapsedTime
      ,(Substring(tt.[dd hh:mm:ss.mss],1,2) * 86400) + (Substring(tt.[dd hh:mm:ss.mss],4,2) * 3600) + (Substring(tt.[dd hh:mm:ss.mss],7,2) * 60) + (Substring(tt.[dd hh:mm:ss.mss],10,2)) as totalseconds
      ,tt.[host_name]
      ,tt.[status]
      ,tt.[CPU]
      ,tt.[program_name]

FROM [MyDB].[DBA].[WhoIsActive] tt
right JOIN ##TEMP kk
on tt.session_id = kk.session_id and tt.collection_time = kk.MaxCollection
--where kk.start_time between @yesterday and @today --and kk.login_name like '%ETL%'
order by tt.login_name asc, tt.session_id asc, login_time desc

Answer

My best chance for unique record is session_id, login_time, login_name and I want the MAX(collection_time) for each unique record.

If you just want the max collection time for each tuple, then use aggregation:

select session_id, login_time, login_name, max(collection_time) max_collection_time
from whoisactive
group by session_id, login_time, login_name

On the other hand, if you want the entire corresponding rows, then one option is row_number():

select *
from (
    select w.*,
        row_number() over(partition by session_id, login_time, login_name order by collection_time desc) rn
    from whoisactive w
) t
where rn = 1