How to select a distinct list where a single reference has 2 sub-references but also has more than 1 of each sub-reference value

Question

I am trying to select a distinct list of TRMANE, TRATSA where there is more than one TRASTA of different values associated to a TRMANE.

Here is an extract from the table; WHTRANP

TRMANE   TRASTA
56819     5   
56819     5   
56819     4   
56833     5   
56833     4   
56844     5   
56844     4   
56880     5   
56880     5   
56880     4   
56880     4

N.B. - As you can see from the above, it's possible there are multiple TRASTAs of the same value against the TRMANE, I think this is what is causing me issues.

Ideally I'd just end up with;

TRMANE   TRASTA
56819     5   
56819     4   
56833     5   
56833     4   
56844     5   
56844     4   
56880     5   
56880     4

If I do this;

SELECT TRMANE, TRASTA   
  FROM WHTRANP           
WHERE TRMANE <> 0        
 GROUP BY TRMANE, TRSTA
 HAVING COUNT(TRASTA) > 2
 ORDER BY 1, 2 DESC

It returns a distinct list of manifests regardless of them having 2 TRASTAS or not - i.e.;

TRMANE   TRASTA
     4     5   
     5     5   
     7     5   
     8     5   
     9     5   
    10     5   
    11     5   
    13     5   
    14     5   
    15     5   
    16     5

Where as this;

SELECT TRMANE as TRMANE, TRASTA AS TRASTA
  FROM WHTRANP                           
WHERE TRMANE <> 0                        
 GROUP BY TRMANE, trasta                 
 HAVING COUNT(DISTINCT TRASTA) > 2       
 ORDER BY 1, 2 DESC

Yields no results.

Do you just want distinct pairs of _everything_? Distinct pairs where there wasn't duplicated data, but multiple of the same `TRMANE`? Distinct pairs only where there was a duplicate `TRMANE, TRASTA` pair? Why do you need this data- why not just de-dup the table? — Clockwork-Muse, Jan 17 '16 at 10:08
There are no duplicates in the table, there are 90 columns in total that make each row distinct - I was just showing that it's possible that there could be more than 1 TRASTA of a single type per TRMANE because I think this affects the count function. I would like a list that ONLY shows TRMANE's that have more than 1 unique TRASTA associated to them. — , Jan 18 '16 at 13:39
...I seriously doubt that your actual (natural) **key** is 90 columns wide. My guess is ~4: Warehouse/Store, Register, and Date/Time/Timestamp (and/or Date + Receipt Number). Anything above about 5 starts getting really unwieldy, and you probably need to extract to another table. Of course, dealing with old flat(er)-files changes things a bit, but even then keys aren't usually that wide... — Clockwork-Muse, Jan 18 '16 at 14:33

score 0 · Answer 1 · answered Jan 15 '16 at 17:01

0

Assuming you version of DB2 supports Windowed Functions:

select *
from 
 (
   SELECT TRMANE, TRASTA, -- distinct combinations
       COUNT(*) OVER (PARTITION BY TRMANE) AS cnt  -- number of rows per TRMANE
     FROM WHTRANP           
   WHERE TRMANE <> 0        
   GROUP BY TRMANE, TRSTA
 ) as dt
WHER cnt > 1 -- more than 1 distinct values
ORDER BY 1, 2 DESC

answered Jan 15 '16 at 17:01

dnoeth

59,503
4
39
56

Gives an error on (PARTITION BY... Says; Token ( was not valid. Valid tokens: , FROM INTO. Would appear it doesn't support windowed functions - it's an iSeries STRSQL command. – Jan 15 '16 at 17:33
1

@RDKells - depending on iSeries version, it _does_ support (a subset of) windowing functions, but not using them with aggregate functions (like `COUNT(...)`). – Clockwork-Muse Jan 17 '16 at 09:43

Clockwork-Muse · Accepted Answer · 2016-01-18T21:47:47.377

Your query is looking for those rows where the trMane is linked to multiple (unique) traStas. On it's own, this is easy:

SELECT trMane
FROM WHTranP
GROUP BY trMane
HAVING COUNT(DISTINCT traSta) > 1

The problem of course is getting out all those traSta values. The grouping removes our ability to output them (barring a windowing function being available), so we need to do something else: join back to the table again.

SELECT DISTINCT WHTranP.trMane, WHTranP.traSta
FROM WHTranP
JOIN (SELECT trMane
      FROM WHTranP
      GROUP BY trMane
      HAVING COUNT(DISTINCT traSta) > 1) Multiples
  ON Multiples.trMane = WHTranP.trMane
ORDER BY WHTranP.trMane, WHTranP.traSta

SQL Fiddle Example

... by joining back to the list we generated earlier, now all we need to do is remove duplicate traSta values, which the outer DISTINCT handles for us readily.

How to select a distinct list where a single reference has 2 sub-references but also has more than 1 of each sub-reference value

2 Answers2