Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete other than top 2 rows for each foreign key element in table. Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 5:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 12:44 AM
Points: 18, Visits: 135
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.

Post #1446414
Posted Thursday, April 25, 2013 5:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
-- 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446426
Posted Thursday, April 25, 2013 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
1 Test Event1 2012-03-20 05:39:51.000
1 Test Event1 2012-03-21 05:39:51.000
2 Test Event2 2012-03-19 05:39:51.000
2 Test Event2 2012-03-24 05:39:51.000
3 Test Event3 2012-03-21 05:39:51.000
3 Test Event3 2012-03-23 05:39:51.000
4 Test Event4 2012-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
1 Test Event1 2012-03-23 05:49:51.000
1 Test Event1 2012-03-22 05:39:51.000
2 Test Event2 2012-03-24 05:39:51.000
2 Test Event2 2012-03-19 05:39:51.000
3 Test Event3 2012-03-23 05:49:51.000
3 Test Event3 2012-03-23 05:39:51.000
4 Test Event4 2012-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

Before posting a performance problem please read
Post #1446452
Posted Thursday, April 25, 2013 6:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446454
Posted Thursday, April 25, 2013 6:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 12:44 AM
Points: 18, Visits: 135
thanks a lot for your prompt response
but my requirement is not to use RowNumber or any other builtin function.
Post #1446468
Posted Thursday, April 25, 2013 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446474
Posted Thursday, April 25, 2013 11:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 12:44 AM
Points: 18, Visits: 135
I am sorry for that , i think i should post in some other forum.
thanks a lot guys for your responses.
Post #1446796
Posted Friday, April 26, 2013 2:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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.



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1446840
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse