CODEWITHSUNDEEP
sql
msadasjwd
msadasjwd

Reputation: 125

Sql get the duplicates with 2 fields but unique with one fields

MNO PNO  PAK
33   44  T
33   44  T
33   44  K
33   45  T
33   46  T
34   46  T
34   47  T
35   47  T

Hello. I am wasting time for hours. I hope i can find the answer.

I want to retrieve for the rows in which, one mno has distinct and different at least 2 pno. And pak always should be t.

So for the given table, only valids are:

MNO PNO  PAK
33   44  T
33   45  T
33   46  T

because other mno's dont have more than 2 different pno with 'T' pak.

I tried this to get at least 3 valid different rows:

SELECT distinct e1. mno,e1. pno   from emq e1
INNER JOIN  emq e2 on (e1.MNO=e2.MNO and e1.PNO <>e2.PNO)
INNER JOIN  emq e3 on (e1.MNO=e3.MNO and e2.MNO=e3.MNO
                                     and e1.PNO <>e3.PNO and e2.PNO <>e3.PNO)
where e1.PAK='T' and e2.PAK='T' and e2.pak='T' and  exists
(SELECT  distinct e4.    pno,MNO from
      emq e4 where e4.pak='T'  group by   e4.    pno,MNO
having count( distinct e4.    pno,HAYMNO) >2);

but it could not do it. The exists part was to validate at least 3 different rows for each mno but cant make it. For example to test,

SELECT distinct e4. pno from emq e4 where e4.pak='T' and HAYMNO=33 group by e4. pno

i did this. it retrieved correctly

44 45 46 but when i add having count

having count(  *) >2

Upvotes: 0

Views: 17

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

How about aggregation and a having clause?

select mno
from emq
group by mno
having min(pno) <> max(pno) and
       min(pak) = max(pak) and
       min(pak) = 'T';

If you want the original data, you can use a join, in, or exists to return them:

select emq.*
from emq join
     (select mno
      from emq
      group by mno
      having min(pno) <> max(pno) and
             min(pak) = max(pak) and
             min(pak) = 'T'
     ) m
     on m.mno = emq.mno;

Upvotes: 1

Related Questions