469,579 Members | 1,157 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Delete records not matching Top25 in GroupBy

I have a table (tblA) that records the RecordID, UserID and
LastViewedDate (DateTime) of each record opened in tblB where RecordID
is the PK in tblB. I want to construct a query that groups all records
in tblA by RecordID, filters by UserID and keeps only the most recent
25 RecordIDs and deletes the rest.

This gets me a recordset of all RecordIDs filtered by UserID in tblA
but I can't figure out how to sort it by LastViewedDate DESC and to
eliminate those not in the Top25:

SELECT RecordID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
Any help is appreciated!
lq

Jul 23 '05 #1
4 1114
You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
where recordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

Jul 23 '05 #2
You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
where recordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

Jul 23 '05 #3
You'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
where recordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

Jul 23 '05 #4
Jason,
Thanks for that solution.
I have to add a small fix to it though for anyone stumbling across this
who might require a similar solution.

delete dbo.tblA
where recordID NOT in
(
select recordID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
AND (UserID = 1234)

Without the "AND (UserID = 1234)" all records regardless of user are
deleted.

Also, users may have to play with the PK of tblA instead of refering to
RecordID which is the PK of tblB, as I had to do to get the results I
wanted:

delete dbo.tblA
where ViewedID NOT in
(
select MaxViewedID
from
(
SELECT top 25
RecordID
, max(LastViewedDate) as lastViewed,
max(ViewedID) as MaxViewedID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID
order by lastViewed desc
) as whaa
)
AND (UserID = 1234)

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by laurenq uantrell | last post: by
9 posts views Thread by Robert Schneider | last post: by
2 posts views Thread by Joe Del Medico | last post: by
3 posts views Thread by klufkee | last post: by
2 posts views Thread by Phil Stanton | last post: by
3 posts views Thread by Phil Stanton | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.