如何在包含最大值的表中查找记录? [英] How to find the record in a table that contains the maximum value?

问题描述

这个问题虽然看起来很简单，但有点棘手.

我有一个包含以下列的表格:

表A:整数标识浮点值日期时间日期varchar(50) 组

我想获取包含按组"列分组的最大日期"的记录的ID"和值".类似于每个组的最新值是多少?"

我可以获得每个组及其最大日期:

SELECT group, MAX(date) FROM A GROUP BY group;-- 我还需要ID"和值"

但我想拥有日期最高的记录的ID"和值.

在 A 和结果之间进行 JOIN 可能是答案，但无法知道 MAX(date) 指的是哪条记录(以防日期"重复).

有人可以帮忙吗?

解决方案

您可以尝试使用子查询

<前>从 A 中选择组、ID、值、日期，其中日期在( 选择 MAX(date) 作为日期从 A一组一组)按组排序

Although this question looks simple, it is kind of tricky.

I have a table with the following columns:

table A:
  int ID
  float value
  datetime date
  varchar(50) group

I would like to obtain the "ID" and "value" of the records that contain the maximum "date" grouped by the column "group". Something like "what is the newest value for each group?"

I can get each group and its maximum date:

SELECT group, MAX(date) FROM A GROUP BY group; -- I also need the "ID" and "value"

But I would like to have the "ID" and value of the record with the highest date.

Making a JOIN between A and the result could be the answer, but there is no way of knowing which record MAX(date) refers to (in case the "date" repeats).

Can someone help?

解决方案

You could try with a subquery

select group, id, value, date from A where date in
( select MAX(date) as date
  from A
  group by group )
order by group

这篇关于如何在包含最大值的表中查找记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！