Join 3 tables and return only distinct values for max datetime

Question

I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it must return all.

SELECT DISTINCT A.CASENO,A.DATE,A.TIME,A.TRANNO,B.PCODE,C.PDESC,A.USER
FROM tableA A
RIGHT JOIN tableB B ON A.CASENO=B.CASENO
RIGHT JOIN tableC C ON C.PCODE=B.PCODE
WHERE A.DATE between 140124 and 140331

Result still have duplicates because not same time and tranno. See result on CASENO.= 08088040

A.CASENO   A.DATE   A.TIME   A.TRANNO   B.PCODE   C.PDESC    A.USER
08088040   140124   182516   321         TYQ      PREPLAN1   ANTHONY
08088040   140124   182131   318         TYQ      PREPLAN1   ANTHONY
36360569   140128   111056   431         CVB      POSTT1     MARIA
36360569   140310   113221   433         CVB      POSTT1     MARIA
37386911   140213   150240   230         M2P      PLANAVG    FELISE
37386911   140213   135220   223         M2P      PLANAVG    HECTOR
39222881   140128    94122   104         TYQ      PREPLAN1   ELLA
40895213   140213   164409   104         CVB      POSTT1     WINNIE
51311866   140124   103203   319         M2P      PLANAVG    BRATT

For same date, same user and same pcode and pdesc, caseno. 08088040 came out twice because of the time and tranno difference. here the max tranno would indicate the latest transaction done on the same case no. the max time also shows that the latest datetime action done for that particular case no. was on 24/01/2014 @ 18:25:16. For the same date and user, I just want the latest transaction details.

I've tried added the below script as the first condition but result was 0. It has also caused long delays before I could get the output.

WHERE A.TRANNO=(SELECT MAX(TRANNO) FROM tableA A where A.CASENO=B.CASENO)

FYI, the rests, eg. case no. 37386911 is considered unique because of different user, so I will want that to be returned. Meanwhile, case no.36360569 is also unique record because different date. although same user.

So, how should I achieve this please? Much of the examples I found was only showing for 1 table and 1 criteria to get distinct values.

The script should be those used for SQL DB2. I actually no background at all in SQL. Most of the query I've done earlier was based on trial and error but it was successful because of no complicated conditions like this.

Hopefully, somebody expert here can share the knowledge.

Answer 1

This is a typical "greatest N per group" problem which is usually solved using window functions:

select caseno, date, time, tranno, pcode, pdesc, user
from (
  select a.caseno,a.date,a.time,a.tranno,b.pcode,c.pdesc,a.user,
         row_number() over (partition by a.caseno order by a.tranno desc) as rn
  from tablea a
    right join tableb b on a.caseno=b.caseno
    right join tablec c on c.pcode=b.pcode
  where a.date between 140124 and 140331
) t
where rn = 1;

You could also use your obfuscated timestamp information to do the sorting:

row_number() over (partition by a.caseno order by a.date desc, a.time desc) as rn

Given your example it won't probably make a difference.

Answer 2

Assuming:

• The record with the max tranno for a case also has the max date/time
• Combination of tranno and caseno is unique

We can use the following query:

SELECT a.caseno
     , a.date
     , a.time
     , a.tranno
     , b.pcode
     , c.pdesc
     , a.user
FROM ( SELECT aa.caseno
            , MAX(aa.tranno) AS tranno
       FROM tablea aa
       WHERE aa.date BETWEEN 140124 AND 140331
       GROUP BY aa.caseno
     ) maxa
JOIN tablea a ON maxa.caseno = a.caseno AND maxa.tranno = a.tranno
JOIN tableb b ON a.caseno = b.caseno
JOIN tableC C ON c.pcode = b.pcode