July 30, 2012 at 11:10 am
It's important to give all the information possible in the first post, otherwise the solutions might not be completely accurate.
Here are 2 solutions with DDL and sample data (something that would have been nice to have, as well). The first one should work on SQLite and many other DBMS, but it's not the optimal way speaking of performance. The second one will work better but only on SQL Server (AFAIK).
DDL & Sample Data
DECLARE @tTabletable(
IDint,
namevarchar( 20),
datedatetime);
INSERT INTO @tTable
SELECT 2812, 'Customer A', '20120101' UNION ALL
SELECT 1275, 'Customer A', '20120102' UNION ALL
SELECT 3472, 'Customer A', '20120103' UNION ALL
SELECT 1245, 'Customer B', '20120101' UNION ALL
SELECT 1544, 'Customer C', '20120101' UNION ALL
SELECT 2567, 'Customer D', '20120101' UNION ALL
SELECT 3446, 'Customer D', '20120102';
Using transferrable code
SELECTm.Master,
t.ID
FROM @tTable t
JOIN ( SELECT t1.ID AS Master,
t1.name,
t1.date
FROM @tTable t1
JOIN (SELECT name,
MIN(date) date
FROM @tTable
GROUP BY name) t2 ON t1.name = t2.name
AND t1.date = t2.date
) M ON t.name = m.name AND m.Master <> t.ID;
Using Common Table Expressions code from Gazareth modifying the order by clause
WITH CTE_Dups AS
(
SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) AS RN
from @tTable
)
SELECT cd.ID AS MasterId, cd2.ID AS SlaveId
FROM CTE_Dups cd
INNER JOIN CTE_Dups cd2 ON cd2.Name = cd.Name AND cd2.RN > 1
WHERE cd.RN = 1;
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply