CODEWITHSUNDEEP
sqlt-sql
BelgoCanadian
BelgoCanadian

Reputation: 934

Minimum of the maximum values

Consider the below results

Datetime1   DateTime2   Customer
2013-06-19  2011-03-30  IP003779    
2014-04-24  2011-03-30  IP003779    
2011-03-30  2009-03-18  IP003779

i need to select the minimum of the first column out of the maximums of the second column. -> 2013-06-19

I'm having a hard time figuring out the query, combining min and max. any thoughts?

Upvotes: 0

Views: 96

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74227

Something like this ought to do, I think, to find the min of the max for each customer:

select Customer        =      t.Customer    ,
       DateTime2_Max   =      t.dt2Max      ,
       DateTime1_Min   = min( x.DateTime1 )
from ( select Customer = Customer ,
              dt2Max   = max( DateTime2 ) 
       from some_table
       group by Customer
     ) t
join some_table x on x.Customer  = t.Customer
                 and x.DateTime2 = t.dt2Max
group by t.Customer ,
         t.dt2Max

If you want to look at the table overall, then it gets simpler:

select DateTime2_Max =      t.dt2Max      ,
       DateTime1_Min = min( x.DateTime1 )
from ( select dt2Max   = max( DateTime2 ) 
       from some_table
     ) t
join some_table x on x.DateTime2 = t.dt2Max
group by t.dt2Max

You could also use windowing functions. Broken out by customer, it looks something like:

select *
from ( select * ,
              rank = row_number() over (
                       partition by Customer
                       order by DateTime2 desc ,
                                DateTime1 asc
                       )
     ) t
where t.rank = 1
order by 1,2,3

And again, simpler if you look at the table as a whole:

select top 1 *
from ( select * ,
              rank = row_number() over (
                       order by DateTime2 desc ,
                                DateTime1 asc
                       )
     ) t
where t.rank = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think this is what you want:

select top 1 *
from table t
order by DateTime2 desc, DateTime1 asc;

Edit:

If you need to do this for all customers, use row_number():

select t.*
from (select t.*,
             row_number() over (partition by customer order by datetime2 desc, datetime1 asc) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions