Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...