Rama Krishna,Yerra

• Aug 2nd, 2006

 

1)By Using Rownum we can do the same mechanisam..

Select rownum,empno,ename,sal from(select rownum,empno,ename,sal from emp order by sal) having rownum=&n;

2) select empno,sal from emp e where &n>(select min(sal) from emp where sa l<e.sal);

Thanks&Regds

Ramki,TCS,DP HYd

Raveendra B N

• Oct 13th, 2006

 

select value from <table name> a

where n = (select count(distinct value)

from <table name> b

where a.value <= b.value)

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 12th, 2006

 

select * from <tablename> where rownum<= &nth

order by sal desc;

minus

select * from <sametablename> where rownum<&nth

order by sal desc;

or

select * from ( select rownum rnum, a.* from <tablename>

order by sal desc)

where rnum = &nth

tina_0091

 Profile Answers by tina_0091 

• Apr 3rd, 2007

 

Use the top-analysis feature which introduce from oracle 8i:

SELECT rownum, empno, ename, sal
FROM (SELECT empno, ename, sal from emp 
           ORDER BY sal )
WHERE rownum < n; (n=any valid integer)

Thanks,
Tina K.

soumyapradh

• Jul 21st, 2007

 

Try this one

SELECT LEVEL, MIN(Sal)
FROM Emp
WHERE LEVEL = &LevelNo
CONNECT BY PRIOR Sal < Sal
GROUP BY LEVEL
/

Ishita

• Oct 4th, 2007

 

Easiest way

select top 1 name from emp where name>(select min(name) from emp) order by name

Ishita.

NITYANAND

• Oct 7th, 2007

 

TO find out nth LOWEST value, e.g. salary from emp table without using ROWNUM & DESC/ASC

SELECT DISTINCT (a.sal) FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT (b.sal))
                       FROM EMP B
                       WHERE a.sal >= b.sal);