Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Last n rows from a table
Hi,
you forgot to mention your *VERSION* of Oracle. Using the built-in analytical functions (new case expression in 8.1.6) of 8.1.6 this would be a query like:
select * from (
select * from emp order by rownum desc)
where rownum <
(select case when max(rownum) > 20 then 21 else 0 end from emp)
union all
select * from emp where rownum <
(select case when max(rownum) > 20 then 0 else 21 end from emp);
The first query returns the last 20 records for tables with more than 20 records, the second query returns the first n records for tables with less than 21 records
Using versions prior to 8.1.6 I can't see a solution resulting in *one* query without user-defined functions. Of course you can query like
select * from (
select * from emp order by rownum desc)
where rownum < constant_value
but this won't allow you to differ between tables > 20 records / < 21 records (I'm not sure, if this query even works in Oracle 7)
Klaus
<sashid99_at_my-deja.com> schrieb in im Newsbeitrag:
8tr3ma$f8p$1_at_nnrp1.deja.com...
> I'd like to retrieve the last 20 rows from a
> table, if the table has more than 20 rows.
>
> Is that possible?
>
> Thanks in Advance.
> Sashi
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Nov 02 2000 - 06:31:32 CST