SQL Query MAX question
I'm writing an application using old Java EJB 2.0 queries, and I'm a bit stuck if there is a way to do what I need in a single query.
Basically, the way I think of this is:
SELECT DISTINCT OBJECT o WITH MAX(o.date) FROM Table AS o WHERE o.name = 'somePassedInName' AND o.date <= 'somePassedInTime'
So I want to grab the record with the same name given, with the largest date that is less than the passed in date.
I'm trying to avoid two queries, or adding logic to grab all the records and then find the one with the latest date, because the number of records returned to my application could be very large. Figured let Oracle do all that
But the two queries would be something like:
SELECT * FROM Table WHERE name = 'somePassedInName' and data <= 'somePassedInTime'
and then from that get the one with MAX(date)
And sorry if this is not the correct forum to post this in, just can't find anything in my searching to answer this
Thanks for any help!
Use ROW_NUMBER ()
And to get the MAX(DATA) select where max_dt = 1.
SELECT a.*, ROW_NUMBER () OVER (ORDER BY DATA DESC) max_dt
FROM tablea a
WHERE NAME = 'somePassedInName'
AND DATA <= 'somePassedInTime';
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Click Here to Expand Forum to Full Width