SQL Query needed to solve poor design

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 - 08:33:09 CST