开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜