Query to eliminate multiple rows based on oldest date
Working in MS Access 2003 SP3: I have a query that I am running to find what 'cars' were sold with a date after the delivery date. I have thousands开发者_如何学运维 of rows. When it is all said and done, I want to just have a handful of rows for each 'car' and then the oldest date. Any suggestions?
CAR DATE ORDERED DATE DELIVERED CUSTOMER NUMBER DATE SOLD
FORD MUSTANG 20061002 20080413 ABC123 20080422
FORD MUSTANG 20061002 20080413 ABC124 20080429
CHEVY IMPALA 20061002 20080413 ABC125 20080505
This could be better if you had an ID field:
DELETE
FROM Cars
WHERE Cars.DATESOLD Not In (
SELECT TOP 5 DateSold
FROM Cars c
WHERE c.Car=Cars.Car
ORDER BY DateSold DESC)
And Cars.DATESOLD Not In (
SELECT TOP 1 DateSold
FROM Cars c
WHERE c.Car=Cars.Car
ORDER BY DateSold)
If there are duplicate dates, you will end up with more than 5 records.
精彩评论