How do you print the last n rows or the first n rows of a table ?

Questions by bharaniprasanth   answers by bharaniprasanth

Editorial / Best Answer

nirmal1in  

  • Member Since Jan-2010 | Jan 17th, 2010

This can be accomplished in following way:

Example: table - emp

For First n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records

For last n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5

 FROM )WHERE .a<

This query can be used for finding nth row also say n = 5

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5

Showing Answers 1 - 24 of 24 Answers

bharaniprasanth

  • Mar 6th, 2008
 

WEll Order by is not hte solution because you donot want the top 10 values of colums..like top 10 rankers in a class. I want the records to be in place as it is  and not sorted...now the last 10 records/rows and first ten rows(this can be acheived throuw row num). please give answer for last ten rows...

  Was this answer useful?  Yes

mehta_dipali

  • Mar 10th, 2008
 

As per my understanding, oracle first fetch result set and then it will number records by rownum. So rownum can not be used to get first/last n rows

select *

from tab , (select count(*)  row_seq_no, <select all columns and alias them to column name_1 >  from tab group by <all columns>) tab1 , (select count(*) total_rows from tab) tab2

where tab.<column_name = tab1.<column_name_1> (for all columns) and row_seq_no <= 10

(for last 10 rows, (tab2. total_rows - row_seq_no <10)

  Was this answer useful?  Yes

bharaniprasanth

  • Mar 12th, 2008
 

Got the working one but partial one indeed

select empno from (select empno,rownum l from emp) where l > (select max(rownum) - 10 from emp);

this will give you the last ten rows...but there is a problem

we have to specify individual column name or field name we want to extract...like empno,empid..But I donot know how to extract all of them at once like we do with

select * from emp; unfortunately select *,rownum from emp doesn't work...Hope to find a soultion for this...

  Was this answer useful?  Yes

mehta_dipali

  • Mar 12th, 2008
 

Can you  try this

select empno from (select * from emp) where rownum > (select max(rownum) - 10 from emp);

by the way, I am still confused. As per my understanind, Oracle first fetch rows from database as per conditions provided and before displaying it assigns ROWNUM...

is that true? If yes, then your query might not produce  correct result......please explain me

  Was this answer useful?  Yes

Geeky2008

  • Mar 31st, 2008
 

The LIMIT command is for MYSQL, Please use the command in proper datasystem system. I think it will solve the problem.

  Was this answer useful?  Yes

atdhdrolla

  • Jun 9th, 2008
 

for SQL server 2005 following is the query and it looks it worked for me

select * from

(select TOP 10 * from <Table_Name> where <Condition> order by [ID] desc)

as table order by table.id


Id is the Primary key in the table

  Was this answer useful?  Yes

Ramya_DS

  • Dec 16th, 2008
 

For top 10
select * from ( select item,pack_ind  from item_master  order  by item_level)where rownum <= 10

For last 10

select * from store_name where rownum <= (select max(rownum) from store) minus select * from storewhere rownum <= (select max(rownum)-5 from store);  

  Was this answer useful?  Yes

godsrikanth

  • Dec 3rd, 2009
 


Last ten:
select l, s.* from (select a.*, rownum l from hz_parties a) s where l > (select max(rownum) - 10 from hz_parties);

first ten
select l, s.* from (select a.*, rownum l from hz_parties a) s where l < (select min(rownum) + 10 from hz_parties);

Thanks
Neo

  Was this answer useful?  Yes

abhijeetprad

  • Dec 14th, 2009
 

For first n row

select * from emp where rownum <=n

For last n row

select * from emp
minus
select * from emp where rownum <=( select count(*)-n from emp)

  Was this answer useful?  Yes

nirmal1in

  • Jan 16th, 2010
 

This can be accomplished in following way:

Example: table - emp

For First n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records

For last n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5

 FROM )WHERE .a<

This query can be used for finding nth row also say n = 5

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5

kanthrirtn

  • Jun 25th, 2010
 

To select last record from a table

select  * from department except
 select top ((select count(*) from department)-1) * from department

  Was this answer useful?  Yes

chaitanya_m03

  • May 19th, 2011
 

First 5 rows:- Select * From (Select * From a12345_count_ex Order By 4 desc) Where rownum < 6;

last 5 rows:-
Select * From (Select * From a12345_count_ex Order By 4) Where rownum < 6;

  Was this answer useful?  Yes

yuvaevergreen

  • Jun 8th, 2011
 

In Teradata,

TOP 5 rows:
sel top 2 * from emp order by salary asc;
Last 5 rows;
sel * from
(sel salary,row_number() over
(order by salary desc) rnk
 from emp) a
 where a.rnk<=5; 

  Was this answer useful?  Yes

deepthi

  • May 17th, 2012
 

Code
  1.  

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions