Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL to retrieve most recent row meeting criteria

SQL to retrieve most recent row meeting criteria

From: Art Krumsee <akrumsee_at_columbus.rr.com>
Date: Thu, 22 Jul 2004 09:45:34 GMT
Message-ID: <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_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? Received on Thu Jul 22 2004 - 04:45:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US