How to get maximum for a field based on a date and

问题:

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

I am reading everything from one table T only.

I am reading 6 fields from that table T.

StartKey
Name
Min
LName
MName
ID

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

but it doesn't work

回答1:

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

回答2:

I don't know if I understood your question correctly, but from what I get you're trying to get a specific set of data.. You can force the filters by getting only the StartKeys you need. This should work:

SELECT StartKey, Name, Min, Lname, Mname, ID 
FROM T 
WHERE StartKey IN ('20130118','20130119','20130120')

If that doesn't work, you can try this:

SELECT StartKey, Name, Min, Lname, Mname, ID
FROM T
WHERE Month(StartKey) >= 1

Assuming that StartKey is a Date field.

回答3:

select startkey, name, min, lname, mname, id
  from (select startkey, name, min, lname, mname, id,
               row_number() over (partition by startkey order by min desc) rn
          from t)
 where rn = 1;

should do that.

eg: http://sqlfiddle.com/#!4/59f6a0/1

note that if you have 2+ rows with the same time and you want to return them all, change row_number to rank. if you only want one of them then row_number is what you want (though you can add extra ordering to pick a specific one.

回答4:

Select *
  From T 
      Inner Join (Select StartKey, Max(Min) AS MaxMinutes
                    From T
                   Group By StartKey
                  ) DerrivedView
    On T.Start = DerrivedView.StartKey And
       T.Min = DerrivedView.MaxMinutes