How to return one row from group by multiple columns

Question

I am trying to extract a list of unique customers from a database where some customers are listed more than once. The (almost) duplicate rows exist because customers have been moved from one division to another or because the customers have been registered with another address (or both).

So my challenge is in data that looks something like this:

ID   Customer   Division   Address
-----------------------------------
1    A          M          X
1    A          L          X
2    B          N          Y
2    B          N          Z
3    C          P          W
3    C          T          S

I want my select statement to return one row for each customer (I dont care which one).

ID   Customer   Division   Address
-----------------------------------
1    A          M          X
2    B          N          Y
3    C          P          W

I am using SQL Server 2008. I think I need to do a "GROUP BY" the last two columns but I don't know how to get just one row out of it.

I hope someone can help me!

(Yes, I know the problem should be solved at the source but unfortunately that is not possible within any reasonable time-frame...).

score 3 · Accepted Answer · answered Aug 13 '13 at 12:05

3

select ID, Customer,Division, Address from 
(
SELECT 
ID, Customer,Division, Address,
ROW_NUMBER() OVER (PARTITON BY Customer Order by Id) as RN
FROM T
) t1
WHERE RN=1

answered Aug 13 '13 at 12:05

valex

23,966
7
43
60

This solved my problem. Thank you very much, valex! I think I - almost - understand how it works. The "t1" makes me wonder? What does that do? Is it just a variable to hold the sub select or ? – Peter_DK Aug 21 '13 at 18:11
@Peter_DK T1 is an alias for the inner subquery. – valex Aug 22 '13 at 06:20

score 1 · Answer 2 · answered Aug 13 '13 at 12:05

Try this one -

DECLARE @temp TABLE
(
      ID INT
    , Customer CHAR(1)
    , Division CHAR(1)
    , [Address] CHAR(1)
)

INSERT INTO @temp (ID, Customer, Division, [Address])
VALUES 
    (1, 'A', 'M', 'X'),
    (1, 'A', 'L', 'X'),
    (2, 'B', 'N', 'Y'),
    (2, 'B', 'N', 'Z'),
    (3, 'C', 'P', 'W'),
    (3, 'C', 'T', 'S')

SELECT t.id
     , t.Customer
     , t.Division
     , t.[Address]
FROM
(
    SELECT *
         , rn = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY 1/0)
    FROM @temp
) t
WHERE T.rn = 1

SELECT ID, Customer, Division = MAX(Division), [Address] = MAX([Address])
FROM @temp
GROUP BY ID, Customer

Output -

id          Customer Division Address
----------- -------- -------- -------
1           A        M        X
2           B        N        Y
3           C        P        W

ID          Customer Division Address
----------- -------- -------- -------
1           A        M        X
2           B        N        Z
3           C        T        W

How to return one row from group by multiple columns

2 Answers2