Posted By: Anonymous
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)
Keywords like TOP, LIMIT, ROWNUM, …etc are database dependent. Please read this article for more information.
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