Re: Latest n rows of table - without a nested select

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Mon, 24 Oct 2005 10:32:23 -0400
Message-ID: <Xf67f.946$yb2.674@news.itd.umich.edu>

Peter Groarke wrote:

> Hi,
> Within oracle SPL, I want to select 2 columns of the latest n rows
> of a table.
> I currently do this with a nested select.
> For example to get columns a and b of the most recent single row of
> table a_journal:
>
> select a,b into p_a, p_b FROM
> (SELECT a, b, from a_journal
> where (c = 'O' or c = 'A')
> order by system_date desc)
> where rownum = 1;
>
> Is there any way of doing this in a single select (i.e. not using a
> nested select)

You could try using a subquery:

SELECT a, b, from a_journal
where (c = 'O' or c = 'A')
and system_date = (select max (system_date) from a_journal

                    where (c = 'O' or c = 'A'))

and rownum = 1;

Not sure if it really buys you anything...

> Note: The table also contains a sequence (the tables primary key).
> Can this be used ?

Yes, but not reliably. Sequance are guaranteed to be unique, but there's no guarantee on the ordering. i.e. the largest value may not be the most recent.

//Walt Received on Mon Oct 24 2005 - 09:32:23 CDT