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

问题描述

我有一个表格包含以下列：

 表A：
 int ID 
浮点值
日期时间日期
 varchar（50）组
  

我想获取包含最大date的记录的ID和value组。像每个组的最新值是多少？

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

SELECT组，MAX（date）FROM GROUP BY组; - 我还需要ID和值

但是，我想拥有记录的ID和值最高的日期。

在A和结果之间进行JOIN可能是答案，但是无法知道MAX（日期）是指哪个记录（如果date重复）

有人可以帮助吗？

解决方案

使用子查询

 
选择组，id，值，从A的日期，其中
中的日期（选择MAX（日期）为日期
从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屋！