A Group DisplayOrder Comparison Sample


http://forums.asp.net/t/1970818.aspx?Comparing+a+collection+of+Rows+with+other+collection+of+rows


declare @UserRecord table
(id int identity(1,1), JobId char(1),categoryId char(1), categoryItem char(1),displayOrder char(1),userId char(1));
insert into @UserRecord values


('1','1', 'a','1', '0'),
('1','1', 'b','2', '0'),
('1','1', 'c','3', '0'),

('1','2', 'a','2', '0'),
('1','2', 'b','1', '0'),
('1','2', 'c','3', '0'),

('1','3', 'a','2', '0'),
('1','3', 'b','1', '0'),
('1','3', 'c','3', '0'),

('1','1', 'a','1', '1'),
('1','1', 'b','2', '1'),
('1','1', 'c','3', '1'),

('1','2', 'a','1', '1'),
('1','2', 'b','2', '1'),
('1','2', 'c','3', '1'),

('1','3', 'a','1', '1'),
('1','3', 'b','2', '1'),
('1','3', 'c','3', '1'),

('1','1', 'a','1', '2'),
('1','1', 'b','2', '2'),
('1','1', 'c','3', '2'),

('1','2', 'a','1', '2'),
('1','2', 'b','2', '2'),
('1','2', 'c','3', '2'),

('1','3', 'a','1', '2'),
('1','3', 'b','2', '2'),
('1','3', 'c','3', '2');

--select * from @UserRecord
;with mycte as (
SELECT Distinct  jobid,categoryid,userId, Cols = STUFF((
SELECT ',' + displayOrder
FROM @UserRecord m
Where m.jobid=m1.jobid AND m.categoryid=m1.categoryid AND m.userId=m1.userId 
Order by id
FOR XML PATH('')), 1, 1, '')
FROM @UserRecord m1

)

,mycte1 as
(
select jobid,categoryid,  cols, userId, count(cols) Over(Partition by categoryid, cols) rn, Min(userid) Over(Partition by categoryid, cols) minUserId 
from mycte
)

 

----If you are using SQL Server 2008 or above

-----Option 1
Merge @UserRecord u
Using ( select jobid,categoryid,  cols, userId from mycte1
Where rn>1 And minUserId=0 and userid>0 ) m 
On (m.jobid=u.jobid AND m.categoryid=u.categoryid AND m.userId=u.userId )
When matched Then
Delete;


----Option 2
--Merge @UserRecord u
--Using (
--select jobid,categoryid, userId from mycte1 m WHERE  exists (Select categoryid, cols From mycte1 
--WHERE userid=0 and categoryid=m.categoryid and m.cols=cols ) and userid<>0 ) m 
--On (m.jobid=u.jobid AND m.categoryid=u.categoryid AND m.userId=u.userId )
--When matched Then
--Delete;

select id, jobid,categoryid,displayOrder, userId from @UserRecord



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s