Re: SQL Query needed to solve poor design

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 Wed Dec 15 1999 - 22:59:34 CST