2

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.

2
  • 1
    "where a.date between 140124 and 140331" - are you really storing dates as integers like that? And why do you split the date and the time into two different (integer) columns? It will be much better to use a proper timestamp column instead.
    – user1822
    Oct 17, 2014 at 7:03
  • 2
    For what it's worth you may as well use INNER JOIN over RIGHT JOIN, your WHERE clause - WHERE A.DATE between 140124 and 140331 will eliminate any NULL values in tableA, effectively turning it into an INNER JOIN anyway.
    – GarethD
    Oct 17, 2014 at 7:28

2 Answers 2

6

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.

5
  • I think they want partition by caseno, a.date, a.user Oct 17, 2014 at 8:17
  • @ypercube: the condition WHERE A.TRANNO=(SELECT MAX(TRANNO) FROM tableA A where A.CASENO=B.CASENO) implies partition by a.caseno to me.
    – user1822
    Oct 17, 2014 at 8:19
  • Yes. But I was reading the "FYI ..." paragraph. Anyway, the description is not so clear, they can tune the query themselves. Oct 17, 2014 at 8:20
  • Hi. I've tested. Came out the below error "Error: SQL0104 - Token ( was not valid. Valid tokens: , FROM INTO. (State:37000, Native Code: FFFFFF98)"
    – DeLL
    Oct 17, 2014 at 10:11
  • @DeLL It seems you are using a DB2 version that does not support window function. I though all DB2 versions - including the host based ones - support this (it is definitely valid for DB2 LUW)
    – user1822
    Oct 17, 2014 at 10:16
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.