Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL to retrieve most recent row meeting criteria
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_at_yyy.com' order by id desc)
where rownum < 2
-- "Art Krumsee" <akrumsee_at_columbus.rr.com> wrote in message news:21MLc.227249$DG4.49690_at_fe2.columbus.rr.com...Received on Thu Jul 22 2004 - 23:11:27 CDT
> 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?
>
>