Re: How to select the <= date row?

Bob Berman wrote: > To solve the problem of joining tables in which a join may or may > not exist, I would suggest performing an outer join. Usually you > do something like: > > select main.id, subtbl_a.data1, subtbl_b.data2 > from main, subtbl_a, subtbl_b > where main.id *= subtbl_a.id > and main.id *= subtbl_b.id> Since this was cross posted to Informix, try this: SELECT <Your data set here> FROM main A, OUTER subtbl_a SA, OUTER subtbl_b SB WHERE A.event_date <= "<Your Date>" AND A.id = SA.id AND A.id = SB.id ORDER BY A.event_date DESCENDING > |>>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. > |>> > It sounds like you may not have a good index. If you can use ISQL /DBACCESS, if this is an Informix problem, then you should be ok. If you try the following: SELECT * FROM main WHERE event_date <= "<YOUR DATE>" ORDER BY event_date DESCENDING And it returns quickly, then there might be something wrong with your optimizer. If so, try this query: SELECT <Your data set here> FROM main A, OUTER subtbl_a SA, OUTER subtbl_b SB WHERE A.id = SA.id AND A.id = SB.id AND A.id IN ( SELECT id FROM main WHERE event_date <= "<YOUR DATE>" ) ORDER BY A.event_date DESCENDING The nested query should reduce the rows that are returned. You may also want to check that you are joining the tables correctly. You need to make sure that the IDs are unique, otherwise the result will have bad data with too many rows. Just my $.02. -Mikey PS, If you only wanted the one row, then use this query: SELECT <Your data set here> FROM main A, OUTER subtbl_a SA, OUTER subtbl_b SB WHERE A.id = SA.id AND A.id = SB.id AND A.id IN ( SELECT id FROM main WHERE MAX(event_date) <= "<YOUR DATE>" )This should return one row, however if this takes a while check your indexes.