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?
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
Use a temp table or table variable and select a distinct list of names into that. Use that structure then to select the top 1 of each record in the original table for each distinct name.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With