Re: SQL Query needed to solve poor design

Am I overlooking something, when I think this will work (sorry, couldn't check, no database nearby at the moment)

select id, max(name) from table
group by id;

Ton

Alan Shein 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 Thu Dec 16 1999 - 11:42:30 CST