如何根据日期获取字段的最大值并获取其他字段? [英] How to get maximum for a field based on a date and get other fields also?

问题描述

我知道我的问题有点令人困惑，但是如果我向您显示数据，它将更加有意义我想要实现的目标.

I know my question is bit confusing but if I show you data it will make more sense what I am trying to achieve.

我只从一个表T中读取所有内容.

I am reading everything from one table T only.

我正在从该表T中读取6个字段.

I am reading 6 fields from that table T.

开始键
名称
最低
LName
MName
ID

StartKey
Name
Min
LName
MName
ID

最上面的表格是数据，最下面的表格是我想要达到的目标. 我需要为每个开始键获取最大值(分钟)
要在输出中包含20130221 .

Top Table is data, and bottom table is what I am trying to achieve. I need to get for each startkey get max(minutes)
FORGOT TO INCLUDE 20130221 in output.

这是我到目前为止尝试过的

Here is what I tried so far

select 
       startkey,
       name,
       min,
       lname,
       mname,
       id
from T
where startkey >= '20130118'
group by startkey,name,lname,mname,id
order by startkey

但是它不起作用

推荐答案

对于每个开始键，您都希望找到最大最小"值.这可以通过嵌套的子查询来完成.然后，仅选择min等于max(min)的地方. 另外，如果您遇到两个记录的startkey和min值相同的情况，则需要解决该问题，否则请同时选择它们.

For each start-key, you want to find the Max "min" value. this can be done via a nested sub-query. Then , select only where the min is equal to the max(min). Also, if you have a situation where two records can have the same value of startkey and min, then you will need to resolve that, otherwise you will select them both.

select  startkey, name, min, lname, mname, id
from T T1
where min = 
 (select max(min) from T T2 where T1.startkey=T2.startkey)

order by startkey

这篇关于如何根据日期获取字段的最大值并获取其他字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！