Re: Getting last rows

Azhdin wrote:

> Hello,
>
> how can i get the last rows from a select which have a rownum restriction :
>
> Select ID_COL from MYTABLE where mycondition and rownum<100 order by ID_COL
> desc;

Hi,
the following example might help you to find a solution for your problem. Here, I'm going to find the employees ranked on position 5 to 8 in a list of employees ordered by their salary. (See the scott/tiger schema.)

With 7.3.4 (no ranks, no inline views), you have to do a little bit of counting for yourselves:
 SELECT e1.ename, e1.sal
 FROM emp e1
 WHERE (SELECT count(*) FROM emp e2 WHERE e1.sal <= e2.sal) between 5 and 8

 ORDER BY e1.sal desc

With 8.1.6, there are the RANK() function and inline views available: SELECT ename, sal, rk
FROM
(SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) as rk  FROM emp)
WHERE rk between 5 and 8;

As to the rownum, this is not useful for windows or "last rows". (Conditions testing for ROWNUM values greater than a positive integer are always false. See your Oracle documentation about this.)

Best Regards, Christoph. Received on Mon Mar 12 2001 - 15:24:21 CST