Re: Similar Rows...

From: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 02 Nov 2000 16:13:15 GMT
Message-ID: <8ts3qj$94a$1@nnrp1.deja.com>

In our last gripping episode slanger_at_dixi-wc.de wrote:
> hi
>
> spool c:\filename (or whatever you want)
>
> select code, count(*) from table_name group by code having count(*) >
 1
>
> hth
> SL
>
> Florent schrieb:
>
> > Hi,
> > I don't know if this has been asked, (sorry if it has ),but I need
 to
> > find 2(or more) rows that are similar,
> > so if I have
> > NAME / CODE
> > A 0
> > B 0
> > C 1
> >
> > and NAME is my primary key, what script can I use to tell that A
 and B
> > are similar based on CODE,
> > If I do SELECT * FROM MY_TABLE WHERE CODE = 0; that 's would give
 me A
> > and B but what if I don't know what code is, I need a script that
 would
> > tell me A and B because CODE is the same regardless of the value.
> >
> > And last but not least, how do I send the result to a file in
 SQLPlus.
> >
> > Thanks in advance
> > Florent
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Not quite the requested result. If I understand the problem you are wanting to return all names with the same code value. Try this (I created a table named 'CDE' to test the script against):

declare

     cursor code_rpt is
     select name, code
     from cde
     order by code, name;

     prev_code   cde.code%type;
begin
     for code_rec in code_rpt loop
         if code_rec.code = prev_code or prev_code is null then
               dbms_output.put(code_rec.name||' ');
               prev_code := code_rec.code;
         elsif code_rec.code != prev_code then
               dbms_output.put_line(prev_code);
               dbms_output.put(code_rec.name||' ');
               prev_code := code_rec.code;
         end if;
     end loop;
     dbms_output.put_line(prev_code);

end;
/

The output is:

A B C 0
D 1
E F G 2

which can be formatted any way one would desire.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Nov 02 2000 - 10:13:15 CST