Re: %%How to <efficiently> do: SELECT MAX(Date)...WHERE Date < myParamDate

You can also use a hint like INDEX_DESC(table_name index_name) and ROWNUM=1 to stop the query at the first row found. Refer to Application Developer's Guide, lookup INDEX_DESC in the index and look at the example. SELECT /*+ INDEX_DESC (rating_tbl index_on_rdate) */   rating
FROM rating_tbl
WHERE
  rdate < TO_DATE('02/15/1997', 'MM/DD/YYYY') AND   ROWNUM = 1; You have to supply the actual index on rdate for 'index_on_rdate'. If you compare the explain plans for your original query using MAX(rdate) and this one using the hint, you'll see the improvement.

-djc
P.S. You messed up your date format.

Sid Soni <soni_at_spam.mindspring.com> wrote in article <61s8pf$vig_at_camel18.mindspring.com>...
> select rating from rating_tbl where rdate =
> (select max(rdate)
> from rating_tbl
> where rdate < to_date('2/15/97','MM-DD-YYYY'));
> Can I be more efficient & do this w/o a subselect?
Received on Tue Oct 14 1997 - 00:00:00 CDT