Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Query needed to solve poor design
Thanks. That did it.
Jeff Guttadauro <jeff109_at_NOSPAM.netscape.net> wrote in message
news:3857b9ff.3216855_at_news...
> Hi, Alan.
>
> Something like this should do it for you:
>
> select c1.idnum, c1.name from company c1 where
> c1.rowid = (select min(rowid) from company c2 where c1.idnum =
> c2.idnum) order by c1.idnum
>
> HTH,
> -Jeff
>
> On Wed, 15 Dec 1999 09:33:09 -0500, "Alan Shein" <alanshein_at_erols.com>
> wrote:
>
> >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.
> >
> >Thanks.
> >
> >
> >
> >
> >
>
Received on Wed Dec 15 1999 - 12:31:27 CST
![]() |
![]() |