Re: SQL to retrieve most recent row meeting criteria

"Art Krumsee" <akrumsee_at_columbus.rr.com> wrote in message news:<21MLc.227249$DG4.49690_at_fe2.columbus.rr.com>...
> I've completed a port of my application to Oracle, SQL Server, MySQL and
> Teradata. That's been a learning effort of considerable proportions. But I
> do have an Oracle question.
>
> I want to retrieve the most recent row from a table meeting a specific
> criteria. In the example below, I want the most recent zipcode of the
> individual with a specific email address. The table has an ID column which
> increments automatically as rows are added. Right now I'm using:
>
> select zip from journal where id=(select max(id) from journal where
> email='xxx_at_yyy.com')
>
> This works but, even with optimized indexing, it requires two hits of the
> database to process. In SQL Server I was able to accomplish this in one hit
> using their TOP syntax. Is there a more efficient way to accomplish this in
> Oracle?

Art, normally I would expect the query to be written something like select whatever from journal A
where email = 'xxxx_at_yyy.com'
and id = (select max(id) from journal B

               where B.email = A.email )

The explan plan will show 2 accesses however the second access is just to the buffers retrieved by the outer query.

HTH -- Mark D Powell -- Received on Thu Jul 22 2004 - 09:57:43 CDT