September 28, 2009 at 3:08 pm
Hello,
I am looking for SQL logic to pick up rows which have 2 columns with different values while all other columns match up.
Ex: I have a table posted below:
Member id Date_submitted Subscribers Location
1 01/19/2009 1234 1
1 03/01/2009 1234 2
1 05/03/2009 1234 3
2 02/03/2009 5678 11
2 03/21/2009 5678 12
I would like to pick up all the duplicate records from the table. The first record with has a member_id of 1 with a date_submitted 01/19/2009 was submitted first so I would treat that as the original one while all other records would be treated as duplicates. Same with the 4th record since that was submitted first I would treat that as original and would have to select the other duplicates.
My result set should be:
Member id Date_submitted Subscribers Location
1 03/01/2009 1234 2
1 05/03/2009 1234 3
2 03/21/2009 5678 12
Hope I was clear. Any advice would be appreciated.
September 28, 2009 at 3:27 pm
Try this:
SELECT *
FROM YourTable t1
WHERE EXISTS(
SELECT *
FROM YourTable t2
WHERE t1.[Member id] = t2.[Member id]
AND t1.Date_submitted > t2.Date_submitted
)
I cannot tell from your description if your Subscribers column is supposed to be part of your primary key or not, so if it is, you should add it in to the column comparisons also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 28, 2009 at 3:34 pm
Similarly, this would also do what you need, I think:
SELECT * FROM(
SELECT
member_id,
subscribers,
date_submitted,
RANK() OVER (PARTITION BY member_id, subscribers ORDER BY date_submitted DESC) as duplicate_ranking
FROM dbo.foo
GROUP BY
member_id,
subscribers,
date_submitted) t1
WHERE t1.duplicate_ranking > 1
This selects member id and subscribers, then ranks them according to date in ascending order. Since they are grouped by member_id and subscribers, any ranking over 1 will be considered a duplicate in your case.
MJM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply