3

My question is how to identify duplicate (repeating) 'groups' of data within an SQL table. I am using SQL Server 2005 at the moment so prefer solutions based on that or ansi-sql.

Here is a sample table and expected result (below) to base this question on:

declare @data table (id nvarchar(10), fund nvarchar(1), xtype nvarchar(1)) insert into @data select 'Switch_1', 'A', 'S' insert into @data select 'Switch_1', 'X', 'B' insert into @data select 'Switch_1', 'Y', 'B' insert into @data select 'Switch_1', 'Z', 'B' insert into @data select 'Switch_2', 'A', 'S' insert into @data select 'Switch_2', 'X', 'B' insert into @data select 'Switch_2', 'Y', 'B' insert into @data select 'Switch_2', 'Z', 'B' insert into @data select 'Switch_3', 'C', 'S' insert into @data select 'Switch_3', 'D', 'B' insert into @data select 'Switch_4', 'C', 'S' insert into @data select 'Switch_4', 'F', 'B'

(new data)

insert into @data select 'Switch_5', 'A', 'S' insert into @data select 'Switch_5', 'X', 'B' insert into @data select 'Switch_5', 'Y', 'B' insert into @data select 'Switch_5', 'Z', 'B' 
-- id fund xtype match -- ---------- ---- ----- --------- -- Switch_1 A S Match_1 -- Switch_1 X B Match_1 -- Switch_1 Y B Match_1 -- Switch_1 Z B Match_1 -- Switch_2 A S Match_1 -- Switch_2 X B Match_1 -- Switch_2 Y B Match_1 -- Switch_2 Z B Match_1 -- Switch_3 C S -- Switch_3 D B -- Switch_4 C S -- Switch_4 F B

(new results)

-- Switch_5 A S Match_1 -- Switch_5 X B Match_1 -- Switch_5 Y B Match_1 -- Switch_5 Z B Match_1

I only want matches on an ALL or NOTHING basis (i.e. All records in the group match all records in another group - not a part match). Any match id can be used (I have used Match_1 above but can be numeric etc.)

Thanks for any help here.

(EDIT: I guess I should add that there could be any number of rows per group, not just the 2 or 4 shown in the sample above - and I'm also trying to avoid cursors)

(EDIT 2: I seem to have an issue if there are more than one matches found. The output from the SQL supplied is returning duplicate records for Switch_1 when there are more than one matches found. I have updated the sample data accordingly. Not sure if Lieven is still following this - I'm also looking at the solution and will post here if found.)

Improve this question

2 Answers 2

Reset to default
2

The flow of execution is as follows

  • q: Combine all funds and xtypes of one id into one string using an XML PATH construction
  • r: Select a ROW_NUMBER and the respective id's for matching groups
  • Select the results by LEFT JOINING @data and r

SQL Statement

;WITH q AS ( SELECT DISTINCT d.id , DuplicateData = STUFF((SELECT ', ' + fund + xtype FROM @data WHERE id = d.id FOR XML PATH('')), 1, 2, '') FROM @data d ) , r AS ( SELECT id1 = q1.id , id2 = q2.id , rn = ROW_NUMBER() OVER (ORDER BY q1.ID) FROM q q1 INNER JOIN q q2 ON q1.DuplicateData = q2.DuplicateData AND q1.id < q2.id ) SELECT id , fund , xtype , match = 'Match_' + CAST(r.rn AS VARCHAR(32)) FROM @data d LEFT OUTER JOIN r ON d.id IN (r.id1, r.id2)

Results

id fund xtype match ---------- ---- ----- -------------------------------------- Switch_1 A S Match_1 Switch_1 X B Match_1 Switch_1 Y B Match_1 Switch_1 Z B Match_1 Switch_2 A S Match_1 Switch_2 X B Match_1 Switch_2 Y B Match_1 Switch_2 Z B Match_1 Switch_3 C S NULL Switch_3 D B NULL Switch_4 C S NULL Switch_4 F B NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Pure genius. Now I have to understand it to adapt it to my real-world data - thanks for this, and for the quick result. I'll mark it as answered once I am sure it will tick all my boxes.
@Sean - Thx but this most likely is not the best performing solution possible. If this is a one time job it's probably not a big deal but if it is meant to run on a regular basis on a large dataset, performance could become an issue.
That should be ok - it is for an end-of-day batch job so performance is not the top priority (though I will have to test with large data sets, but I think it will be fine for purpose)
Some good answers there, but I think this solution is more suited to solving my problem, so am marking it as the answer. Thanks again for all replies.
1

Here it is another query for it:

create table #temp1 ( id varchar(10), fund nvarchar(1), xtype nvarchar(1) ) insert into #temp1 select 'Switch_1', 'A', 'S' insert into #temp1 select 'Switch_1', 'X', 'B' insert into #temp1 select 'Switch_1', 'Y', 'B' insert into #temp1 select 'Switch_1', 'Z', 'B' insert into #temp1 select 'Switch_2', 'A', 'S' insert into #temp1 select 'Switch_2', 'X', 'B' insert into #temp1 select 'Switch_2', 'Y', 'B' insert into #temp1 select 'Switch_2', 'Z', 'B' insert into #temp1 select 'Switch_3', 'C', 'S' insert into #temp1 select 'Switch_3', 'D', 'B' insert into #temp1 select 'Switch_4', 'C', 'S' insert into #temp1 select 'Switch_4', 'F', 'B' select t1.*, case when t2.equal = t3.total then 'True' else 'False' end as 'Match' from #temp1 t1 left outer join (select m.id, count(m2.id) as 'equal' from #temp1 m inner join #temp1 m2 on m.Id <> m2.Id and m.fund = m2.fund and m.xtype = m2.xtype group by m.id) t2 on t1.id = t2.id inner join (select m3.id, count(m3.fund) as 'total' from #temp1 m3 group by m3.id) t3 on t3.id = t1.id drop table #temp1
Improve this answer

6 Comments

I've put True or False when there is a match! You can test the query here: data.stackexchange.com/stackoverflow/query/new
This is looking good but you still somehow have to identify what records belong to what group. If you change Switch_4, F, B to Switch_4, D, B you will get all True (correct) but you don't know what connects with what.
@Lieven I dind't though that was necessary, but Sean already has the needed answer :)
perhaps but this might be altered to add those requirements and prove to be faster.
@both - actually, what I will land up having to do is parse the data through non-sql code - I need the individual records, but also a flag to know if those records are part of a repeated group for aggregation. To be honest, knowing the matching id would be useful, but I can order by the matching column and aggregate while they contain the same value. (I possibly should have stated my expected outcome in the original question). Thanks to you too aF.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.