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
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