Re: I need your help!!

In article <1993Feb27.214115.7506_at_oracle.us.oracle.com> rmanalac_at_oracle.com writes:
>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).

I thought what you wanted was more complex than what you do want. I think that the simple:

SELCT * FROM daily_ntf
WHERE (tracking_num, test) IN
 (SELECT tracking_num, text FROM daily_ntf   GROUP BY tracking_num, test
  HAVING count(tracking_num) = 1 AND

         count(test) = 1);

If you had wanted 2,3,5 and either 1 or 4, well then SQL is not so helpful.

Mark Received on Sun Feb 28 1993 - 04:53:57 CET