Re: I need your help!!

dowling_at_dub-tse.NoSubdomain.NoDomain (Dowling) writes:
|> I want is to not select rows that have the same tracking num AND
|> the same test.
|>
|> E.g.
|> Tracking_num Test P_F Station
|> 10 1 f 1020
|> 11 1 f 1030
|> 12 2 f 1030
|> 10 1 f 1030
|> 10 2 f 1020
|>
|> In the above I want to be able to select the records 2, 3 and 5
|> and not select the records 1 and 4, with tracking_num and test =
|> 10 and 1 respectively. My current method will only select records
|> 2 and 3.
|> My way at the moment is:
|> SQL> l
|> 1 select * from daily_ntf
|> 2 where
|> 3 tracking_num = ANY (select tracking_num
|> 4 from daily_ntf
|> 5 group by tracking_num
|> 6 having count(tracking_num) = 1
|> 7 )
|> 8* order by pg_station_id,test_code,tracking_num

There may be other more elegant methods, but the only one I can think of so far is to substitute "tracking_num" with "tracking_num||'x'||test" everywhere in your above query (except in the order by clause).

Actually here is yet another way that is essentially the same but easier to read:
select * from d
 where (a, b) = any (select a, b
 from d
 group by a, b
 having count(a||'x'||b) = 1);

Roderick Manalac
Oracle Corporation Received on Sat Feb 27 1993 - 22:41:15 CET