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 :rolleyes:

Thanks for any help!

Try this:

Code:

---

SELECT a.*, ROW_NUMBER () OVER (ORDER BY DATA DESC) max_dt
  FROM tablea a
 WHERE NAME = 'somePassedInName'
  AND DATA <= 'somePassedInTime';

---

And to get the MAX(DATA) select where max_dt = 1.
;)