Re: HELP! Oracle script for identify identical records

On Jul 18, 5:41 pm, "ko" <ko..._at_singnet.com.sg> wrote:
> I have a table which consists of n columns. I need to list those identical
> records.
> The coditions for identical records are column A and column B of those
> records must contain the same value and column C must contain 'Y'.
> Can someone enlighten me how to write an Oracle SQL script to accomplish
> this?
>
> Many thanks for your help.

select *
  from t t1
 where c = 'Y'
   and exists (select null

                 from t t2
                where t2.a = t1.a -- same a
                  and t2.b = t1.b -- same b
                  and t2.c = 'Y'
                  and t1.rowid != t2.rowid -- different row
              )

or

select *
  from t
 where c = 'Y'
   and (a,b) in (select a,b

                   from t
                  where c = 'Y'
                  group by a,b -- group records with same a,b
                 having count(*) > 1 -- choose groups with more then 1
row
                )

S. Received on Thu Jul 19 2007 - 08:25:43 CDT