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 -> Re: Last n rows from a table

Re: Last n rows from a table

From: Klaus Zeuch <KZeuchnospam_at_hotmail.com>
Date: Thu, 2 Nov 2000 13:31:32 +0100
Message-ID: <8trpid$o4j$1@papyrus.erlm.siemens.de>

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

Original text of this message

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