Re: SQL Query needed to solve poor design

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 - 09:58:18 CST