4

I'm building up on the question referenced here: SQL - How to select a row having a column with max value

date                 value

18/5/2010, 1 pm        40
18/5/2010, 2 pm        20
18/5/2010, 3 pm        60
18/5/2010, 4 pm        30
18/5/2010, 5 pm        60
18/5/2010, 6 pm        25

i need to query for the row having max(value)(i.e. 60). So, here we get two rows. From that, I need the row with the lowest time stamp for that day(i.e 18/5/2010, 3 pm -> 60)

How can we build on the answer as provided by Sujee:

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc)
where rownum <= 1

if the data has a 3rd column "category".

date                 value    category

18/5/2010, 1 pm        40      1
18/5/2010, 2 pm        20      1
18/5/2010, 3 pm        60      1
18/5/2010, 4 pm        30      2
18/5/2010, 5 pm        60      2
18/5/2010, 6 pm        25      2

FYI - I'm using Oracle, and trying to avoid a nested join (hence the rownum trick)

The goal is to have the same answer, but with a group by category

Community
  • 1
  • 1
Stephane Maarek
  • 5,202
  • 9
  • 46
  • 87

2 Answers2

4

It sounds like you want to select the row with the highest high_val for each category. If so, you can use row_number() to rank each row within a category according to its high_val value, and only select the highest ranked rows i.e. rn = 1:

select * from (
    select row_number() over (partition by category order by high_val desc, date asc) rn, *
    from mytable
    where something = 'avalue'
) t1 where rn = 1
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
0

Just add an additional key to the order by:

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc, date asc
     )
where rownum = 1;

If you want category in the result set, then select it in the subquery.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786