Re: SQL Query needed to solve poor design

On 16 Dec 1999 04:59:34 GMT, "fumi" <fumi_at_tpts5.seed.net.tw> wrote:

Question at end of message.

>
>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;
>

I like that one a lot. Good stuff...

>In Oracle8i, you can use:
>
>select id#, (select name from company where id#=a.id# and rownum=1)
> from company a;
>

Isn't this one just a more streamlined way of writing a correlated subquery? Just wondering if I'm unclear on the concept...

Thanks,
-Jeff Received on Thu Dec 16 1999 - 09:20:14 CST