Re: SQL question

Try this,

select dup_col1, dup_col2, ....... other columns which are different depending on 1st or last occurrence
  from table1 outer
 where rowid > (select min(rowid)

                    from  table1
                   where dup_col1   = outer.dup_col1
                     and dup_col2 = outer.dup_col2 )

Thing to remember is rowids increase for the rows added later.

Therefore the above command would give you all the records but not the first one.

Changing to rowid = (select min(rowid) should give you the 1st occurrence.

Changing to rowid = (select max(rowid) should give you the last occurrence.

Please check with examples, before you use it

Regards !!!

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

R Fray <russ_at_u-net.net> wrote in article <36002dba.95308595_at_news.u-net.com>...
>
> Hi,
>
> I can use select max(xyz) and min(xyz) to select the maximum and
> minimum occurence of a column within a table.
>
> What I need to do however is select the first or last occurence of a
> column within a table, to get rid of a duplicate value.
>
> Ie, if I use :
> insert into table1 (select value from table2 where a=b);
>
> - I get 'ORA-01427: single-row subquery returns more than one row',
> because there can be multiple instances of a equalling b.
>
> I can't use :
> insert into table1 (select max(value) from table2 where a=b);
>
> Because 'value' can be the same in more than one instance, hence using
> max does not weed out duplicated.
>
> Is there something equivalent to :
> insert into table1 (select first(value) from table2 where a=b);
>
> So that I can select the first instance and ignore all others?
>
> I'm really stuck on this and would appreciate some urgent help.
>
> Please cc replies to russ_at_u-net.net
>
> Thanks,
> Russell Fray.
>
>
>
> --
>
> Russell Fray
> U-NET Ltd.
>
Received on Fri Sep 18 1998 - 10:37:10 CDT