Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to return one row from group by multiple columns

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

like image 952
Peter_DK Avatar asked Oct 04 '22 06:10

Peter_DK


2 Answers

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
like image 137
valex Avatar answered Oct 05 '22 19:10

valex


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
like image 34
Devart Avatar answered Oct 05 '22 19:10

Devart