mahi328

 Profile Answers by mahi328 Questions by mahi328

• Aug 17th, 2006

 

nth salary

select salary

from table_name a

where &n=(select count(salary) from table_name b where a.salary<=b.salary);

n salaries

select salary

from table_name a

where &n>=(select count(salary) from table_name b where a.salary<=b.salary);

tdas2004

 Profile Answers by tdas2004 

• Sep 7th, 2006

 

1. select empno,a from (select empno, rownum a from emp order by empno) where a= &temp;

2.select empno,a from (select empno, rownum a from emp order by empno) where a <= &temp

kamalnomula

 Profile 

• Sep 11th, 2006

 

select sal from tablename where sal=(select sal from tablename =n);

pratyush

• Oct 22nd, 2006

 

1- nth row from table

select * from emp where rownum<='&give_nth rows'

2- last nth rows

select *
from emp
where rownum<=(select count(empno)
from emp
)
minus
select *
from emp
where rownum<=(select count(empno)-'&give_numberof _rows'
from emp
)

deepak singh

• Nov 7th, 2006

 

select * from aj a where rownum <= (select count(rownum) from aj b where a.SCRIP_CD = b.scrip_cd if you want to reterive ist row from the tableselect * from aj a where rownum = (select count(rownum) from aj b where a.SCRIP_CD > b.scrip_cd)if you want to reterive last row from the table

Vamseek

 Profile Answers by Vamseek Questions by Vamseek

• Nov 10th, 2006

 

Use distinct to get accurate results...

nth salary

select salary

from table_name a

where &n=(select count(distinct salary) from table_name b where a.salary<=b.salary);

n salaries

select salary

from table_name a

where &n>=(select count(distinct salary) from table_name b where a.salary<=b.salary);

sudhaker

• Dec 25th, 2006

 

here u go....

select * from emp where rowid in(select max(rowid) from emp where rownum<='&n')

Nikhil_4_Oracle

• Mar 6th, 2007

 

HI All,

Just Try table alias ,

Select * from(Select rownum r,e.* from emp e)emp
Where r=5
/

Bye...

Nikhil

amitsrivastava115

 Profile Answers by amitsrivastava115 

• Jul 16th, 2008

 

to get  the first n number of rows we can use

select * from emp where rowid in (select rowid from emp
 where rownum <= &n
 minus
 select rowid from emp
 where rownum < 1);

in the same way to get the last n number of rows i.e last five or last two we can use

select * from emp
  where rowid in (select rowid from emp
  where rownum <= (select count(empno) from emp)
  minus
  select rowid from emp
  where rownum <= (select count(empno) - &n from emp));


also to get the last row inserted in the table we can use

select * from emp where rowid in (select max(rowid) from emp);


to get  the first row inserted we can use
select * from emp where rowid in (select max(rowid) from emp);

hope that helps

amitsrivastava115

 Profile Answers by amitsrivastava115 

• Jul 16th, 2008

 

to get the first row inserted use min(rowid) in place of max(rowid)

sunjer

 Profile Answers by sunjer 

• Jul 27th, 2008

 

Here you go...

Top n rows:

select *
from (select sal from emp order by 1 desc)
where rownum < 5;

Bottom n rows

select *
from (select sal from emp order by 1)
where rownum < 5;

vishakha_jain81

 Profile Answers by vishakha_jain81 

• Aug 7th, 2008

 

for nth record

select employee_id, r from (select employee_id, rownum r from emp order by employee_id) where r= &nth;

for first n record

select employee_id,r from (select employee_id, rownum r from emp order by employee_id) where r <= &n;

for last n records

select employee_id from (select employee_id,rownum r from (select employee_id from emp order by employee_id desc) )where r<=n ;

sandip01

 Profile Answers by sandip01 Questions by sandip01

• May 25th, 2010

 

 Select ename,sal from emp
 where sal=(select max(sal) from emp
      Where level=&level connect by prior sal>sal
 group by level);

This is the query for getting nth sal

aashaashok

 Profile Answers by aashaashok 

• Jun 29th, 2010

 

To select the nth(say 14th) row from the table without any conditions we can use following query

select * from employee where rownum <= 14 minus

select * from employee  where rownum <= 13

To select last/first n(say 3) records, we can use Top N queries as follows

select * from (select * from employee order by emp_no) where rownum<=3

select * from (select * from employee order by emp_no desc) where rownum<=3

mahamood khan

 Profile Answers by mahamood khan 

• Jul 16th, 2010

 

Selecting first or last n rows can be done by using inline views as follows:

SELECT rownum, salary FROM (SELECT salary FROM employees ORDER BY salary DESC)
WHERE rownum<=n

Above query is for top salary holders, for lowest salary holders remove desc as the default option for order by clause is ascending.

Desired rows can be retrieved by specifying the rownum in WHERE clause, like rownum=5

kiran_marla

 Profile Answers by kiran_marla 

• Jul 18th, 2010

 

Nth ROW

SELECT * FROM (SELECT ROWNUM RN, EMP.* FROM EMP) WHERE RN = &RN;

FIRST N ROWS

SELECT * FROM EMP WHERE WHERE ROWNUM <= &N;

LAST N ROWS

SELECT * FROM (SELECT ROWNUM RN, EMP.* FROM EMP ORDER BY RN DESC) WHERE ROWNUM <= &N;

kiran_marla

 Profile Answers by kiran_marla 

• Jul 18th, 2010

 

Nth ROW

SELECT * FROM (SELECT ROWNUM RN EMP.* FROM EMP) WHERE RN =  &N;

FIRST N ROWS

SELECT * FROM EMP WHERE WHERE ROWNUM <= &N;

LAST N ROWS

SELECT * FROM (SELECT ROWNUM RN EMP.* FROM EMP ORDER BY RN DESC) WHERE ROWNUM <= &N;

katgeektalk

 Profile Answers by katgeektalk 

• Aug 26th, 2010

 

For any row

select * from
  (select b.*, rownum rn from (select * from scott.emp) b )
  where rn = '&N'

p.mamta

 Profile Answers by p.mamta Questions by p.mamta

• Aug 30th, 2010

 

How to get the nth row from the table ?

select distinct(Salary),* from Employee A where n=(select count(distinct(Salary)) from Employee b where b.Salary <= a.Salary);

how to  select  first n rows from the table ?

SELECT ename, sal
  FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
           FROM emp )
WHERE sal_rank <= 10;

how to  select  last n rows from the table ?

SELECT ename, sal
  FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
           FROM emp )
WHERE sal_dense_rank <= 10;

sandip.dhopat

 Profile Answers by sandip.dhopat Questions by sandip.dhopat

• Jun 9th, 2011

 

Select nth Row:

select * from
(select t1.c1, t1.c2, .... , rank(col_name) over (order by col_name) rnk
from table_name t1)
where rnk = n;

To select top n rows in a order.

select * from
(select t1.c1, t1.c2, .... , rank(col_name) over (order by col_name) rnk
from table_name t1)
where rnk <= n;

To select bottom n rows in a order.
select * from
(select t1.c1, t1.c2, .... , rank(col_name) over (order by col_name) rnk
from table_name t1)
where rnk >= n;