Re: How to select the <= date row?

From: Mike Segel <mikey_at_segel.com>
Date: 1996/06/08
Message-ID: <31B9A2DB.75AE_at_segel.com>#1/1

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. Received on Sat Jun 08 1996 - 00:00:00 CEST