December 30, 2011 at 6:57 pm
Hello Everyone
I am in need of help with creating a query that will work. I am not having any luck with that part.
DECLARE @Member TABLE
(
MemberID bigint
,ControlID bigint
)
INSERT INTO @Member
SELECT 712372226, 20111230108660 UNION ALL
SELECT 712397728, 20111230108661 UNION ALL
SELECT 712418037, 20111230108662 UNION ALL
SELECT 712440031, 20111230108663 UNION ALL
SELECT 712440379, 20111230108664 UNION ALL
SELECT 713908424, 20111230108720 UNION ALL
SELECT 713908424, 20111230108766
SELECT * FROM @Member
This issue is very similar to a query that I posted last week, but still different enough that I am having trouble.
As you can see, I have a duplicate MemberID [713908424]. I need to remove only the one with the larger ControlID [20111230108766], and keep all the others. If there are multiple MemberID's I need to keep a distinct list of MemberIDs that have the MIN(ControlID)
There could be a chance that there may be multiple duplicates per the memberID. So I need this to be able to handle that issue. Or I would simply use a delete statement with a where clause
Thank You in advance for your help and comments.
Andrew SQLDBA
December 30, 2011 at 7:25 pm
I have a feeling I might be missing some subtlety here, but is this close to what you need?
DECLARE @Member TABLE
(
MemberID BIGINT NOT NULL,
ControlID BIGINT NOT NULL,
PRIMARY KEY (MemberID, ControlID)
)
;
INSERT @Member
(MemberID, ControlID)
VALUES
(712372226, 20111230108660),
(712397728, 20111230108661),
(712418037, 20111230108662),
(712440031, 20111230108663),
(712440379, 20111230108664),
(713908424, 20111230108720),
(713908424, 20111230108766)
;
WITH Numbered AS
(
SELECT
*,
rn = ROW_NUMBER() OVER (
PARTITION BY MemberID ORDER BY ControlID ASC)
FROM @Member
)
DELETE Numbered
WHERE
rn > 1
;
SELECT * FROM @Member AS m
;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 2, 2012 at 6:05 am
CREATE TABLE MEMBER
(
MemberID bigint
,ControlID bigint
)
INSERT INTO MEMBER
SELECT 712372226, 20111230108660 UNION ALL
SELECT 712397728, 20111230108661 UNION ALL
SELECT 712418037, 20111230108662 UNION ALL
SELECT 712440031, 20111230108663 UNION ALL
SELECT 712440379, 20111230108664 UNION ALL
SELECT 713908424, 20111230108720 UNION ALL
SELECT 713908424, 20111230108766;
SELECT * FROM MEMBER
WITH RESULT
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY memberid ORDER BY CONTROLID ASC )AS NUMBER ,* FROM MEMBER
)
DELETE FROM RESULT WHERE NUMBER >= 2
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 2, 2012 at 6:23 am
Thanks Everyone
Kiwi
your code worked perfectly
Andrew SQLDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply