从按特定字段分组的日期列中获取最大值 [英] Fetch Max from a date column grouped by a particular field

问题描述

我有一个与此类似的表:

I have a table similar to this:

LogId  RefId   Entered
==================================
1      1       2010-12-01
2      1       2010-12-04
3      2       2010-12-01
4      2       2010-12-06
5      3       2010-12-01
6      1       2010-12-10
7      3       2010-12-05
8      4       2010-12-01

在这里，LogId是唯一的；对于每个RefId，都有多个带有时间戳的条目.我要提取的是每个最新RefId的LogId.

Here, LogId is unique; For each RefId, there are multiple entries with timestamp. What I want to extract is LogId for each latest RefId.

我尝试了以下链接的解决方案:http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column.但是，它返回具有相同RefId的多行. LogId和RefId应该是唯一的.

I tried solutions from this link:http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column. But, it returns multiple rows with same RefId. The LogId as well as RefId should be unique.

有人可以帮我吗?

谢谢

Vamyip

推荐答案

您需要使用一个子查询，该子查询为每个RefId提取最新的Entered值，然后将您的源表与此RefId一起输入，输入:

You need to use a subquery that extracts the latest Entered value for each RefId, and then join your source table to this on RefId, Entered:

SELECT DISTINCT MyTable.LogId, MyTable.Entered FROM MyTable
INNER JOIN (SELECT RefId, MAX(Entered) as Entered FROM MyTable GROUP BY RefId) Latest
ON MyTable.RefId = Latest.RefId AND MyTable.Entered = Latest.Entered

这篇关于从按特定字段分组的日期列中获取最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！