How to select a row based on the maximum value of a column, when ties are expected

Posted on

Question :

SUBSCRIBER  DATE         TIME            VALUE
aaa         23-04-2015   04:04:07        10
aab         23-04-2015   12:04:32        5
bbb         23-04-2015   01:04:05        20
bbb         23-04-2015   12:04:20        20
bbb         23-04-2015   12:04:31        20
ccc         23-04-2015   05:04:00        10

I need a query to get the subscriber details based on the maximum value (and the minimum time if there are ties on max(value)) for a particular subscriber.

Answer :

select subscriber, date_column, time_column, value from (
    subscriber, date_column, time_column, value,
    rank() over (partition by subscriber order by value desc, time_column) as r
  from mytab
) where r = 1
and subscriber = 'ccc';

select a.value
from your_table a
where a.value = (select max(value) from your_table)
and a.time = (select min(b.time) from your_table b where b.value = a.value)

That being said a description of the table would help. Is Time a date time value or a string?

The following should work as per your requirement. However, if there is a tie on the time too, it would pick a random row. You may want to choose a combination that would give you a unique row.

select myTable.* from myTable INNER JOIN 
    (select top 1 Value, Time
from myTable where subscriber = 'aaa'
order by Value desc, Time ) TT
ON myTable.Value = TT.Value
and myTable.time = TT.time
where subscriber = 'aaa'

Leave a Reply

Your email address will not be published. Required fields are marked *