SQL - How to select a row having a column with max value

Question

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)

Answer 1

Keywords like TOP, LIMIT, ROWNUM, ...etc are database dependent. Please read this article for more information.

http://en.wikipedia.org/wiki/Select_(SQL)#Result_limits

Oracle: ROWNUM could be used.

select * from (select * from table  order by value desc, date_column)  where rownum = 1; 

Answering the question more specifically:

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

Answer 2

Analytics! This avoids having to access the table twice:

SELECT DISTINCT        FIRST_VALUE(date_col)  OVER (ORDER BY value_col DESC, date_col ASC),        FIRST_VALUE(value_col) OVER (ORDER BY value_col DESC, date_col ASC) FROM   mytable;