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