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@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 Krumsee" <akrumsee@columbus.rr.com> wrote in message news:<21MLc.227249$DG4.49690@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@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@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 --

Art Krumsee wrote:
> 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@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?

I won't make any claims regarding performance but analytics can do this ...

select *
from (
select last_value(zip) over (order by id)
from journal
where email = 'xxx@yyy.com'
)
where rownum = 1

--
Richard Kuhler

Art Krumsee wrote:

> 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@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?

Even in SQL Server you were hitting twice. They just hid one of the
hits.

That said ... if ID is indexed you aren't hitting the table anyway ...
you are hitting the index if the system is working properly.

Daniel Morgan

All kinds of ways to do everything.... not saying this is the best, but here
is another option ...

select * from
(select zip from journal where email='xxx@yyy.com' order by id desc)
where rownum < 2

--

"Art Krumsee" <akrumsee@columbus.rr.com> wrote in message
news:21MLc.227249$DG4.49690@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@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?
>
>