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!
lqYou'll need an extra IN clause to get a single ID out, but it's pretty
straightforward (don't forget the NOT!):

delete Record
whererecordID 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/|||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
whererecordID 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/|||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
whererecordID 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/|||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)