I would like to return a single row per id and per date, which is the max/last value for that day - max/last value for a particular day is given by its time. This is how much I got so far

select distinct a.AU_ID, MAX([DATE]   ' '   [TIME]) as 'TIMESTAMPfull'
from TABLE_B b join TABLE_A a on a.AU_ID = b.AU_ID
where a.col like 'A%' and [DATE] BETWEEN 20210408 AND 20220408 and a.AU_ID = 'xxXXxx00001'
group by a.AU_ID, b.col1, b.col2, b.col3

Returns me this, as you can see for 20220111 this returns 5 rows, but i only need the one occured last on 20220111:

Goal is to return only 1 row per [DATE] --> row 6 from the table above for 20220111. Expected result:

CodePudding user response：

If I understand correctly you can try to use ROW_NUMBER window function in the subquery.

SELECT AU_ID,
       TIMESTAMPfull
FROM (
    SELECT a.AU_ID,
            [DATE]   ' '   [TIME] TIMESTAMPfull,
            ROW_NUMBER() OVER(PARTITION BY [DATE], ORDER BY [TIME] DESC) rn
    FROM TABLE_B b 
    JOIN TABLE_A a on a.AU_ID = b.AU_ID
    WHERE a.col like 'A%' 
    AND [DATE] BETWEEN 20210408 AND 20220408
    AND a.AU_ID = 'xxXXxx00001'
) t1
WHERE rn = 1