Re: How to select the <= date row?

Selecting the max(date) <= specified date:

>>Two things make this a particularly challenging problem:
>>
>> 1) This query has to be done from a query tool, so we are
 restricted to
 a
>> single SQL statement (unless Business Objects can build a
 report from
>> more than one SQL statement).
>>
>>2) Selecting the max(specified_date) where event_date <=
 specified_date
>> works fine, but it will take a couple of days to wade through
 40
>> million rows.
>

  The best solution is to have an descending index on the event_date. Then, max(event_date) <= specified date will use the index to go right to the correct row (it is equivalent to an ascending index with the predicate min(event_date) >= specified date). With DB2, this should require a single index access.   For a further discussion of designing and accessing date fields, see my DB2 tips pages at http://members.gnn.com/JoeDB2/db2main.html Received on Fri Jun 07 1996 - 00:00:00 CEST