Thanks. I'll give your method a try. We're still on 7.
fumi <fumi_at_tpts5.seed.net.tw> wrote in message
news:839rjm$bsf$3_at_news.seed.net.tw...
>
> Alan Shein <alanshein_at_erols.com> wrote in message
news:8388r7$o4q$1_at_autumn.news.rcn.net...
> > I need to create a report from a table that was not properly normalized
and
> > has no Primary Key. There is an ID number, a name, and then other
> > information. All I am interested in is any one instance of any name
> > associated with an ID number. The data is something like this:
> >
> > ID# Name Other Info
Columns
> > 1000 Acme Piano Company
> > 1000 Acme Piano Co.
> > 1001 Johnson Wax Candle Co.
> > 1002 Mickey Dee's Hamburger Stand
> > 1000 Acme Piano Co. Inc.
> >
> >
> > What I need as a result is
> >
> > 1000 Acme Piano Company (any of the 3 versions of the name will do)
> > 1001 Johnson Wax Candle Co.
> > 1002 Mickey Dee's Hamburger Stand
> >
> > I don't need anything from the "Other Info Columns", but they may or may
not
> > have distinct information in them, so they can't be used in the query.
> >
> > I suspect I need a correlated subquery with a rownum=1 in there
somewhere,
> > but I can't figure it out.
>
>
> If you want to use correlated subquery, you can use:
>
> select id#, name
> from company a
> where rowid=(select min(rowid) from company where id#=a.id#)
>
> But correlated subqueries are not efficient ways,
> it's better to use the following ways:
>
> In Oracle 7.1.x or above, you can use:
>
> select c.id#, c.name
> from company c, (select min(rowid) row_id from company group by id#) r
> where c.rowid=r.row_id;
>
> In Oracle8i, you can use:
>
> select id#, (select name from company where id#=a.id# and rownum=1)
> from company a;
>
>
>
>
Received on Thu Dec 16 1999 - 10:54:29 CST
