MAX Date for each row

Question

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

score 1 · Accepted Answer · answered Nov 27 '19 at 02:02

1

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

or

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

answered Nov 27 '19 at 02:02

Ed Bangga

12,879
4
16
30

The first one works like a charm, the second one groups the results by user but shows 3 results, I guess because of the three users who touched the serial. – carchis Nov 27 '19 at 02:18
If I want to remove the serial and apply that query to the entire table, what would I remove? – carchis Nov 27 '19 at 02:19
Then 2nd query will only work depends on your data, but if you want to add additional columns, the 1st query is the ideal one. – Ed Bangga Nov 27 '19 at 02:20
From your first answer, will I be able to run that query with other tables? Like left join a price table? – carchis Nov 27 '19 at 02:41
yes, you can do left join, it doesnt care of other tables. – Ed Bangga Nov 27 '19 at 02:44
I posted a new question related to this question if you could help me answer it. – carchis Nov 27 '19 at 03:54

score 0 · Answer 2 · answered Nov 27 '19 at 02:24

I would recommend ORDER BY and FETCH FIRST:

SELECT serh.*
FROM SERH    
WHERE HVSERN = '519488536' 
ORDER BY HVTDAT DESC
FETCH FIRST 1 ROW ONLY;

If you want this for all HVSERN values, then ROW_NUMBER() is a reasonable approach. In many databases, a correlated subquery is faster:

SELECT s.*
FROM SERH s   
WHERE s.HVTDAT = (SELECT MAX(h2.HVDAT)
                  FROM SERH s2
                  WHERE s2.HVSERN = s.HVSERN
                 );

score 0 · Answer 3 · answered Nov 27 '19 at 02:29

You have to get the aggregation first then use the result to join back to you source data to get the remaining pieces of data you need (HVUSER in this case). Try this, it should do the trick.

select
    T1.HVSERN,
    T1.dt,
    T2.HVUSER
from (
select
    HVSERN,
    MAX(HVTDAT) as dt
from
    SERH 
group by
    HVSERN) T1

inner join SERH T2 on T1.HVSERN = T2.HVSERN and T1.dt = T2.HVTDAT
;

MAX Date for each row

3 Answers3