Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL-02112 SELECT ... INTO returns too many rows --- how to use the SELECT_ERROR option??
"Dave Sutton" <dpsutton_at_marchsystems.co.uk> writes:
> Thanks very much for this.
>
> I've been able to use this method on most of my queries but on some the
> ordering is critical and the ROWNUM kicks in before the ordering is taken
> into account.
>
> Any further ideas??
in 8i you can do select * from (select ... order by ...) where rownum == 1;
In 8.0.5 there's no way unless you're willing to count on the ordering that the query plan guarantees. Specifically, if you can ensure the query is doing an index range scan then you can be sure you're getting the first record in that range. That's not unlikely given that rownum normally kicks Oracle into first_rows mode so merge joins et al aren't common.
-- gregReceived on Thu Jan 11 2001 - 08:41:33 CST