Search code examples
sqlt-sqlsql-server-2000

SQL query to return one single record for each unique value in a column

I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data.

Behold, [Addresses]:

Name         Street                 City          State
--------------------------------------------------------
Bob          123 Fake Street        Peoria        IL
Bob          234 Other Street       Fargo         ND
Jim          345 Main Street        St Louis      MO

This is actually a simplified example of the structure of the actual table. The structure of the table is completely beyond my control. I need a query that will return a single address per name. It doesn't matter which address, just that there is only one. The result could be this:

Name         Street                 City          State
--------------------------------------------------------
Bob          123 Fake Street        Peoria        IL
Jim          345 Main Street        St Louis      MO

I found a similar question here, but none of the solutions given work in my case because I do not have access to CROSS APPLY, and calling MIN() on each column will mix different addresses together, and although I don't care which record is returned, it must be one intact row, not a mix of different rows.

Recommendations to change the table structure will not help me. I agree that this table is terrible, (it's worse than shown here) but this is part of a major ERP database that I can not change.

There are about 3000 records in this table. There is no primary key.

Any ideas?

Solution

  • Well, this will give you pretty bad performance, but I think it'll work

    SELECT t.Name, t.Street, t.City, t.State
FROM table t 
INNER JOIN (
     SELECT m.Name, MIN(m.Street + ';' + m.City  + ';' + m.State) AS comb
     FROM table m
     GROUP BY m.Name
) x
   ON  x.Name = t.Name
   AND x.comb = t.Street + ';' + t.City  + ';' + t.State