tags:

views:

63

answers:

1

How can I select the maximum row from a table? What does maximum mean -- well my table has two timestamp columns, TIME1 and TIME2. The maximum column is the one with the latest value for TIME1. If that is not a unique row, then the maximum is the one within those rows with the latest value for TIME2.

This is on Oracle if that matters.

+2  A: 

What you need is a "Top-N" query:

select * from ( select * from table order by time1 desc, time2 desc ) where rownum < 2;

if you properly index on time1, time2 it will be very fast:

http://blog.fatalmind.com/2010/07/30/analytic-top-n-queries/

Markus Winand
Why is this marked correct? It orders by time1,time2 ASCENDING, which means the opposite of "the latest value for time".
Jeffrey Kemp
@Jeffrey Kemp: Oh yes, thank you. If someone edits I'll mark it correct again.
jjujuma
right, desc added twice.
Markus Winand