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...).

valex · Accepted Answer · 2013-08-13 12:05:36Z

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, 2013 at 12:05

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter_DK Over a year ago

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 ?

valex Over a year ago

@Peter_DK T1 is an alias for the inner subquery.

Devart · Accepted Answer · 2013-08-13 12:05:31Z

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

Collectives™ on Stack Overflow

How to return one row from group by multiple columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related