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

"Peter Groarke" <peter.groarke_at_orbiscom.com> schrieb im Newsbeitrag news:1130162388.796734.323010_at_f14g2000cwb.googlegroups.com...
> 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)
> Note: The table also contains a sequence (the tables primary key).
> Can this be used ?
>
> Any help appreciated,
> Peter
>

Well, you can also use analytic function row_number(). select a,b
from

(select a, b, row_number() over(order by system_date desc) rn

from a_journal

where c = 'O' or c = 'A'

order by system_date desc)

where rn = 1.

The nested selects have you also, but I find this query more flexible.

For example , you can find all records, except most recent.

select a,b

from

(select a, b, row_number() over(order by system_date desc) rn

from scott.a_journal

where c = 'O' or c = 'A'

order by system_date desc)

where rn > 1,

you can find secons and third row:

where rn between 2 and 3

and so on.

Regards

Dmytro Dekhtyaryuk Received on Mon Oct 24 2005 - 09:19:40 CDT