MAX Date for each row

I am having some difficulty at writing a syntax that will provide me with the most recent date for several results. Little bit of background, I am pulling live data from an IBM AS400, I have no SQL tables, I am connecting to the tables via ODBC, using QTODBC to create my queries, then I export them to Power Bi. For what I hear, pulling data from AS400 tables is a little different from SQL queries, not that much.

This is how the data looks like, Multiple transaction for that Serial number in different dates.

enter image description here

I can get the most recent date with omitting the HVUSER.

select    
HVSERN,    
MAX(HVTDAT) as Date    
From SERH    
Where HVSERN = '519488536'  (there are thousands of other serials, tens of thousands of transactions)    
Group by HVSERN

HVSERN               HVTDAT

519488536            11/26/2019

When I add the column “HVUSER” who last touched the serial, I am no longer able to keep the last transaction. Can anyone help me to figure this one out? I’ve been told I need to nest a query, do subquery to clean up the old dates?
what I am looking for is.

HVSERN         HVTDAT       HVUSER

519488536     11/26/2019    VG55

Answer

use row_number(), db2 supports this.

select * from (
    select HVSERN, row_number() over (partition by HVSERN order by HVTDAT desc) as rn, HVUSER
    from SERH 
    Where HVSERN = '519488536') t where t.rn = 1

select    
   HVSERN, HVUSER,
   MAX(HVTDAT) as Date    
From SERH    
Where HVSERN = '519488536' 
Group by HVSERN, HVUSER

Advertisement

Answer