snippetMinor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

themaximumcolumnaretiesexpectedvaluebasedhowselect

Problem

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.

Solution

select subscriber, date_column, time_column, value from (
  select 
    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';

Code Snippets

select subscriber, date_column, time_column, value from (
  select 
    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';

Context

StackExchange Database Administrators Q#105890, answer score: 3

Revisions (0)

No revisions yet.