SQL - How to select row with column with maximum value (+ group)

Question

I raise the question mentioned here: SQL - How to select row with column with maximum 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 a string having max (value) (i.e. 60). So here we get two lines. From this I need the line with the least timestamp for that day (i.e. 18/5/2010, 15:00 → 60)

How can we be based on the answer provided by Suji:

 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 am using Oracle and trying to avoid a nested connection (hence the rownum trick)

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

Answer 1

It looks 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 in the category according to its high_val value and select only 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 

Answer 2

Just add an extra key to 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 a category in a result set, then select it in the subquery.