Detele query keeping 2 most maximum records

  • Thread starter Thread starter Norman Belanger
  • Start date Start date
N

Norman Belanger

Hi,

Is there an easy way to remove records from a table by keeping the 2 most
maximum records. I have records that are being updated daily where one of
the field is stamped with a fiscal month code identifier such as 401, 402,
403 etc. If records are being updated with 403, I want records holding 401
to be removed. I have it going but using too many steps and thought there
was an easy way.

Thanks

Norm
 
Hi,


To get those top 2


SELECT a.*
FROM myTable As a
WHERE a.pk IN( SELECT TOP 2 b.pk
FROM myTable As b
WHERE a.grouping = b.grouping
ORDER BY b.valuable DESC)


to erase (untested)

DELETE a.*
FROM myTable As a
WHERE a.pk NOT IN( ... )



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top