Path: text.usenetserver.com!out03a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!o38g2000hse.googlegroups.com!not-for-mail
From:  William Robertson <williamr2019@googlemail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: OPTIMIZER_HINT, FIRST_ROWS, and MAX
Date: Sun, 11 Nov 2007 08:27:49 -0800
Organization: http://groups.google.com
Lines: 49
Message-ID: <1194798469.484493.218870@o38g2000hse.googlegroups.com>
References: <1194648180.079344.243280@57g2000hsv.googlegroups.com>
NNTP-Posting-Host: 82.45.190.94
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1194798469 8716 127.0.0.1 (11 Nov 2007 16:27:49 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sun, 11 Nov 2007 16:27:49 +0000 (UTC)
In-Reply-To: <1194648180.079344.243280@57g2000hsv.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; rv:1.8.1.9) Gecko/20071025 Firefox/2.0.0.9,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: o38g2000hse.googlegroups.com; posting-host=82.45.190.94;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.misc:250571
X-Received-Date: Sun, 11 Nov 2007 11:27:49 EST (text.usenetserver.com)

On Nov 9, 10:43 pm, atha...@gmail.com wrote:
> I am getting completely different results with the following query:
>
> SELECT scanA.* FROM
> SCHEDWIN.LOCSCAN scanA WHERE  scanA.SCAN_TIME = ( SELECT
> MAX(scanB.SCAN_TIME)
> FROM SCHEDWIN.LOCSCAN scanB  WHERE scanA.RESID||scanA.LIBENTRYID =
> scanB.RESID||scanB.LIBENTRYID AND
> (scanB.SCAN_TIME <  ' 1194645483' ) );
>
> ...depending on whether I have OPTIMIZER_HINT set to CHOOSE or
> FIRST_ROWS!
> (CHOOSE returns the correct result set, FIRST_ROWS returns nothing.)
>
> Can anyone explain why this might be happening and how I can get my
> desired result set with the CHOOSE option?
>
> I am trying to select all the records (for a given RESID/LIBENTRYID
> combination) that are the LATEST records (according to the SCAN_TIME
> field) before a certain cutoff value.
>
> Thank you!
> -Aung

Queries of the form
WHERE a||b = x||y
an unreliable at best, and so is

scanB.SCAN_TIME <  ' 1194645483'

although I am surprised to hear you get inconsistent results.

What is the datatype of locsan.scan_time?

What happens if you rationalise the query to

SELECT scanA.*
FROM   schedwin.locscan scanA
WHERE  scanA.scan_time =
       ( SELECT MAX(scanB.scan_time)
         FROM   schedwin.locscan scanB
	 WHERE  scanA.resid = scanB.resid
	 AND    scanA.libentryid = scanB.libentryid
	 AND    scanB.scan_time < ' 1194645483' );

I agree with the other comments though, none of this speculation means
very much without the Oracle version and perhaps (I'm guessing here)
Forms version.