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

Question

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?

Answer 1

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

Answer 2

select distinct Name , street,city,state
from table t1 where street =  
(select min(street) from table t2 where t2.name = t1.name)

Answer 3

A slight modification on the above should work.

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

Now this won't work if you have the same street but the other pieces of information are different (e.g. with typos).

OR a more complete hash would include all the fields (but you likely have too many for performance):

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

Answer 4

I don't think that you can do that, given your constraints. You can pull out distinct combinations of those fields. But if someone spelled Bob and Bobb with the same address you'd end up with two records. [GIGO] You are correct that any grouping (short of grouping on all of the fields-equivalent to DISTINCT) will mix rows. It's too bad that you don't have a unique identifier for each customer.

You might be able to nest queries together in such as way as to select the top 1 for each name and join all of those together.

Answer 5

select Name , street,city,state FROM( select Name , street,city,state, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS rn from table) AS t WHERE rn=1

Answer 6

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.

Answer 7

SELECT name,
       ( SELECT TOP 1 street, city, state
           FROM addresses b
          WHERE a.name = b.name )
  FROM addresses a
 GROUP BY name

Answer 8

SELECT name, street, address, state FROM (SELECT name, street, address, state, DENSE_RANK() OVER (PARTITION BY name ORDER BY street DESC) AS r FROM tbl) AS t WHERE r = 1;

Answer 9

A temporary table solution would be as follows

CREATE Table #Addresses
(
 MyId int IDENTITY(1,1),
 [Name] NVARCHAR(50),
 Street NVARCHAR(50),
 City NVARCHAR(50),
 State NVARCHAR(50)
)

INSERT INTO #Addresses ([Name], Street, City, State) SELECT [Name], Street, City, State FROM Addresses

SELECT
 Addresses1.[Name],
 Addresses1.Street,
 Addresses1.City,
 Addresses1.State
FROM
 #Addresses Addresses1
WHERE
 Addresses1.MyId =
(
 SELECT
  MIN(MyId)
 FROM
  #Addresses Addresses2
 WHERE
  Addresses2.[Name] = Addresses1.[Name]
)

DROP TABLE #Addresses

Answer 10

This is ugly as hell, but it sounds like your predicament is ugly, too... so here goes...

select  name,
 (select top 1 street from [Addresses] a1 where a1.name = a0.name) as street,
 (select top 1 city from [Addresses] a2 where a2.name = a0.name) as city,
 (select top 1 state from [Addresses] a3 where a3.name = a0.name) as state
from    (select distinct name from [Addresses]) as a0

Answer 11

If you can use a temp table:

select * -- Create and populate temp table 
into #Addresses
from Addresses 

alter table #Addresses add PK int identity(1, 1) primary key

select Name, Street, City, State 
-- Explicitly name columns here to not return the PK
from #Addresses A
where not exists 
    (select *
    from #Addresses B
    where B.Name = A.Name
    and A.PK > B.PK)

This solution would not be advisable for much larger tables.

Answer 12

I think this is a good candidate for a cursor based solution. It's been so long since I've used a cursor that I won't attempt to write the T-SQL but here's the idea:

1. Create temp table with same schema as Addresses
2. Select distinct Names into cursor
3. Loop through cursor selecting top 1 from Addresses into temp table for each distinct Name
4. Return select from temp table

Answer 13

And still another way:

-- build a sample table  
DECLARE @T TABLE (Name VARCHAR(50),Street VARCHAR(50),City VARCHAR(50),State VARCHAR(50))  
INSERT INTO @T   
SELECT 'Bob','123 Fake Street','Peoria','IL' UNION  
SELECT 'Bob','234 Other Street','Fargo','ND' UNION  
SELECT 'Jim','345 Main Street','St Louis','MO' UNION  
SELECT 'Fred','234 Other Street','Fargo','ND'  

-- here is all you do to get the unique record  
SELECT * FROM @T a WHERE (SELECT COUNT(*) FROM @T b WHERE a.Name = b.name and a.street <= b.street) = 1