select maximum score grouped by date, display full datetime

Question

Gday, I have a table that shows a series of scores and datetimes those scores occurred. I'd like to select the maximum of these scores for each day, but display the datetime that the score occurred.

I am using an Oracle database (10g) and the table is structured like so:

    scoredatetime                score (integer)
    ---------------------------------------
    01-jan-09 00:10:00      10
    01-jan-09 01:00:00      11
    01-jan-09 04:00:01      9
    ...

I'd like to be able to present the results such the above becomes:

 01-jan-09 01:00:00      11

This following query gets me halfway there.. but not all the way.

select 
   trunc(t.scoredatetime), max(t.score)
from 
   mytable t
group by
   trunc(t.scoredatetime)

I cannot join on score only because the same high score may have been achieved multiple times throughout the day.

I appreciate your help!

Simon Edwards

Answer 1

You might need two SELECT statements to pull this off: the first to collect the truncated date and associated max score, and the second to pull in the actual datetime values associated with the score.

Try:

SELECT T.ScoreDateTime, T.Score
FROM
(
 SELECT
    TRUNC(T.ScoreDateTime) ScoreDate, MAX(T.score) BestScore
 FROM 
    MyTable T
 GROUP BY 
    TRUNC(T.ScoreDateTime)
) ByDate
INNER JOIN MyTable T 
    ON TRUNC(T.ScoreDateTime) = ByDate.ScoreDate and T.Score = ByDate.BestScore
ORDER BY T.ScoreDateTime DESC

This will pull in best score ties as well.

For a version which selects only the most recently-posted high score for each day:

SELECT T.ScoreDateTime, T.Score
FROM
(
 SELECT
    TRUNC(T.ScoreDateTime) ScoreDate, 
    MAX(T.score) BestScore, 
    MAX(T.ScoreDateTime) BestScoreTime
 FROM 
    MyTable T
 GROUP BY 
    TRUNC(T.ScoreDateTime)
) ByDate
INNER JOIN MyTable T 
    ON T.ScoreDateTime = ByDate.BestScoreTime and T.Score = ByDate.BestScore
ORDER BY T.ScoreDateTime DESC

This may produce multiple records per date if two different scores were posted at exactly the same time.

Answer 2

with mytableRanked(d,scoredatetime,score,rk) as (
  select
    scoredatetime,
    score,
    row_number() over (
      partition by trunc(scoredatetime)
      order by score desc, scoredatetime desc
    )
  from mytable
)
  select
    scoredatetime,
    score
  from mytableRanked    
  where rk = 1
  order by date desc

In the case of multiple high scores within a day, this returns the row corresponding to the one that occurred latest in the day. If you want to see all highest scores in a day, remove scoredatetime desc from the order by specification in the row_number window.

Alternatively, you can do this (it will list ties of high score for a date):

select
  scoredatetime,
  score
from mytable
where not exists (
  select *
  from mytable as M2
  where trunc(M2.scoredatetime) = trunc(mytable.scoredatetime)
  and M2.score > mytable.scoredatetime
)
order by scoredatetime desc

Answer 3

First of all, you did not yet specify what should happen if two or more rows within the same day contain an equal high score.

Two possible answers to that question:

1) Just select one of the scoredatetime's, it doesn't matter which one

In this case don't use self joins or analytics as you see in the other answers, because there is a special aggregate function that can do your job more efficient. An example:

SQL> create table mytable (scoredatetime,score)
  2  as
  3  select to_date('01-jan-2009 00:10:00','dd-mon-yyyy hh24:mi:ss'), 10 from dual union all
  4  select to_date('01-jan-2009 01:00:00','dd-mon-yyyy hh24:mi:ss'), 11 from dual union all
  5  select to_date('01-jan-2009 04:00:00','dd-mon-yyyy hh24:mi:ss'), 9 from dual union all
  6  select to_date('02-jan-2009 00:10:00','dd-mon-yyyy hh24:mi:ss'), 1 from dual union all
  7  select to_date('02-jan-2009 01:00:00','dd-mon-yyyy hh24:mi:ss'), 1 from dual union all
  8  select to_date('02-jan-2009 04:00:00','dd-mon-yyyy hh24:mi:ss'), 0 from dual
  9  /

Table created.

SQL> select max(scoredatetime) keep (dense_rank last order by score) scoredatetime
  2       , max(score)
  3    from mytable
  4   group by trunc(scoredatetime,'dd')
  5  /

SCOREDATETIME       MAX(SCORE)
------------------- ----------
01-01-2009 01:00:00         11
02-01-2009 01:00:00          1

2 rows selected.

2) Select all records with the maximum score.

In this case you need analytics with a RANK or DENSE_RANK function. An example:

SQL> select scoredatetime
  2       , score
  3    from ( select scoredatetime
  4                , score
  5                , rank() over (partition by trunc(scoredatetime,'dd') order by score desc) rnk
  6             from mytable
  7         )
  8   where rnk = 1
  9  /

SCOREDATETIME            SCORE
------------------- ----------
01-01-2009 01:00:00         11
02-01-2009 00:10:00          1
02-01-2009 01:00:00          1

3 rows selected.

Regards, Rob.