查询以获取重复行中的最新条目?我正在使用Access DB [英] Query to Get the Latest entry in duplicate rows?I am using Access DB

问题描述

我有一个包含3列CardNo，VehicleNO，ExpiryDate的数据表.我必须通过将ExpiryDate与当前日期进行比较来获得这些列.现在CardNo和VehicleNo是每行中的重复项，并且ExpiryDate更改(同一日期的日期或时间)我想要的是我应该能够在那些重复行中获得最新输入的ExpiryDate.我想要访问查询..

I have a datatable with 3 columns CardNo,VehicleNO,ExpiryDate..I have to get those columns by comparing ExpiryDate with the present Date. Now CardNo and VehicleNo are Duplicates in each row and ExpiryDate changes(either Date or Time on the same date)What i want is I should be able to get the latest enterd ExpiryDate in those Duplicate Rows.I want access Query for that..

cardno vehicleno    expirydate
12	ap20aa4779	06/18/12 11:50:06
14	ap20aa4777	06/05/12 11:20:36
14	ap20aa4777	06/15/12 10:46:20
14	ap20aa4777	06/15/12 10:49:20
14	ap20aa4777	06/15/12 10:55:06
14	AP20AA4777	06/18/12 11:11:00
9523	AP123123	06/18/12 13:09:24

现在的输出应该是

now the output should be

12	ap20aa4779  06/18/12 11:50:06
 14    AP20AA4777    06/18/12 11:11:00
9523 AP123123        06/18/12 13:09:24

推荐答案

这应该可行:

This should works:

SELECT CardNo, VehicleNO, MAX(ExpiryDate) AS ExpiryDate
FROM YourTable
GROUP BY CardNo, VehicleNO
ORDER BY CardNo, VehicleNO

尝试一下:

Try this:

SELECT CardNo, VehicleNo, expiryDate
     FROM YourTableName t1
     WHERE expiryDate = (SELECT MAX(expiryDate)
                     FROM YourTableName t2
                    WHERE t1.CardNo = t2.CardNo)

这篇关于查询以获取重复行中的最新条目?我正在使用Access DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！