选择每个ID具有最新日期的数据 [英] Select data with latest date for each id

问题描述

我目前正在使用Microsoft Access，但我正在努力做自己想做的事情.我有这张桌子A:

I am currently working on Microsoft Access an I am struggling to do what I want. I have this table A:

Table A

id    title  name   date
123   azer   dfgd   1
123   afg    qsd    5
123   arr    poi    7
123   aur    qhg    3
456   aoe    aer    3
456   iuy    zer    4

我想获取每个ID具有最新日期(最高编号)的ID，标题和名称列

And I would like to get the columns id,title and name that have the latest date (highest number) for each id

在该示例中，查询将给出

With that example, the query would give

id  title name date
123  arr  poi   7
456  iuy  zer   4

希望您能为我提供帮助.

I hope you'll be able to help me.

提前谢谢！

推荐答案

我建议使用相关子查询:

I would recommend a correlated subquery:

select a.*
from a
where a.date = (select max(a2.date) from a as a2 where a2.id = a.id);

为了提高性能，您需要在 a(id，date)上建立索引.

For performance, you want an index on a(id, date).

这篇关于选择每个ID具有最新日期的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！