Re: SQL to retrieve most recent row meeting criteria

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

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 Received on Thu Jul 22 2004 - 20:12:53 CDT