Delete other than top 2 rows for each foreign key element in table.

Question

Post reply

Delete other than top 2 rows for each foreign key element in table.

faheemahmad14

SSC Veteran

Points: 246
More actions
April 25, 2013 at 5:04 am

#293033

I have a table like that
MemberId(F.K) ! Event ! Event Date
1 'Test Event1' "2012-03-20 05:39:51"
1 'Test Event1' "2012-03-21 05:39:51"
1 'Test Event1' "2012-03-22 05:39:51"
3 'Test Event3' "2012-03-23 05:39:51"
2 'Test Event2' "2012-03-24 05:39:51"
2 'Test Event2' "2012-03-19 05:39:51"
1 'Test Event1' "2012-03-23 05:49:51"
3 'Test Event3' "2012-03-23 05:49:51"
4 'Test Event4' "2012-03-27 05:39:51"
3 'Test Event3' "2012-03-21 05:39:51"
. . .
. .
and what i require is to keep only two latest events for each members and to delete the rest ones.
i.e.
1 'Test Event1' "2012-03-23 05:49:51"
1 'Test Event1' "2012-03-22 05:39:51"
2 'Test Event2' "2012-03-24 05:39:51"
2 'Test Event2' "2012-03-19 05:39:51"
3 'Test Event3' "2012-03-23 05:39:51"
3 'Test Event3' "2012-03-23 05:49:51"
4 'Test Event4' "2012-03-27 05:39:51"
I have an idea of doing like that by using CTE or by using RowNumbers and Partitions but i have to avoid the power of DBMS , Cursors and to write a pure SQL for that ,
any help will be appreciated.
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- Always run a SELECT first to see which rows are affected.

-- This query should return the rows you want to keep

SELECT MemberId, [Event], [Event Date]

FROM (

SELECT MemberId, [Event], [Event Date],

rn = ROW_NUMBER() OVER (PARTITION BY MemberId ORDER BY [Event Date])

FROM MyTable

) d

WHERE rn < 3

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

ChrisM@Work (4/25/2013)
-- Always run a SELECT first to see which rows are affected. -- This query should return the rows you want to keep SELECT MemberId, [Event], [Event Date] FROM ( SELECT MemberId, [Event], [Event Date], rn = ROW_NUMBER() OVER (PARTITION BY MemberId ORDER BY [Event Date]) FROM MyTable ) d WHERE rn < 3 The above returns MemberId Event Event_Date 1Test Event12012-03-20 05:39:51.000 1Test Event12012-03-21 05:39:51.000 2Test Event22012-03-19 05:39:51.000 2Test Event22012-03-24 05:39:51.000 3Test Event32012-03-21 05:39:51.000 3Test Event32012-03-23 05:39:51.000 4Test Event42012-03-27 05:39:51.000 modifying this line of code: rn = ROW_NUMBER() OVER (PARTITION BY MemberId ORDER BY [Event_Date] ) to be: rn = ROW_NUMBER() OVER (PARTITION BY MemberId ORDER BY [Event_Date] DESC ) Returns: MemberId Event Event_Date 1Test Event12012-03-23 05:49:51.000 1Test Event12012-03-22 05:39:51.000 2Test Event22012-03-24 05:39:51.000 2Test Event22012-03-19 05:39:51.000 3Test Event32012-03-23 05:49:51.000 3Test Event32012-03-23 05:39:51.000 4Test Event42012-03-27 05:39:51.000

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thanks Ron.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

faheemahmad14 SSC Veteran Points: 246 More actions · Answer 4

thanks a lot for your prompt response

but my requirement is not to use RowNumber or any other builtin function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

faheemahmad14 (4/25/2013)
thanks a lot for your prompt response
but my requirement is not to use RowNumber or any other builtin function.

ROW_NUMBER is ideal for this - can you explain why you can't use it? You've posted in the SQL Server 2008 forum section.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

faheemahmad14 SSC Veteran Points: 246 More actions · Answer 6

I am sorry for that , i think i should post in some other forum.

thanks a lot guys for your responses.

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

Sample data, in case anyone else wants to have a go: -

SELECT [MemberId(F.K)], [Event], [Event Date]

INTO #yourTable

FROM (VALUES(1, 'Test Event1', '2012-03-20 05:39:51'),

(1, 'Test Event1', '2012-03-21 05:39:51'),

(1, 'Test Event1', '2012-03-22 05:39:51'),

(3, 'Test Event3', '2012-03-23 05:39:51'),

(2, 'Test Event2', '2012-03-24 05:39:51'),

(2, 'Test Event2', '2012-03-19 05:39:51'),

(1, 'Test Event1', '2012-03-23 05:49:51'),

(3, 'Test Event3', '2012-03-23 05:49:51'),

(4, 'Test Event4', '2012-03-27 05:39:51'),

(3, 'Test Event3', '2012-03-21 05:39:51')

)a([MemberId(F.K)], [Event], [Event Date]);

Solution: -

SELECT [MemberId(F.K)], [Event], [Event Date]

FROM #yourTable a

WHERE [Event Date] IN (SELECT TOP 2 [Event Date]

FROM #yourTable b

WHERE a.[MemberId(F.K)] = b.[MemberId(F.K)] AND a.[Event Date] <= b.[Event Date]

ORDER BY [Event Date] DESC

);

Be aware that this is infinitely worse than using the ROW_NUMBER function.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/