Top 10 Rows

parag100

how i retrive top or bottom 10 rows in a table in oracle ?
give select statement ...

Apr 12 '07 #1

chandu031

Hi,

This query will return the top 10 rows..

select col1,col2 from (select rank() over (order by col1) r , col1,col2 from TABLE) where r<11



For bottom 10 rows use

select col1,col2 from (select rank() over (order by col1 DESC) r , col1,col2 from TABLE) where r<11


Hope this helps..............

Apr 12 '07 #2

masdi2t

for top row just use the magic column rownum

SELECT * FROM your_table WHERE rownum <= 10;

if you want bottom row you can order it before (using ORDER BY your_field DESC)

Apr 13 '07 #3

Sandya

select top 10 column name from table name

Apr 13 '07 #4

Sandya

/* it fetch u top 10 elements */
select top 10 column name from table name order by column name desc

/it vl fect u bottom 10 elements */
select top 10 column name from table name order by column name asc

Apr 13 '07 #5

Sandya

v can use top in sqlserver 2000
but it is not possible in oracle


it vl fetch u data of top 10 elment

select columname from tablename group by rownum ,column name
having rownum >enter the rownum above which u want
order by rownum ,colum name desc

Apr 13 '07 #6

chandu031

Hi ,

As Sandya pointed out there is no TOP function in Oracle(atleast till version 9i)..
So you can either use a rank() function or use rownum.

And one clarification ...
Use rank() over(order by column desc) for top 10 rows and
rank() over(order by column) for bottom 10 rows...

And if you have contention between two or more rows and you want all of these rows to be displayed , then use Dense_rank() function. For example if you have four people with salaries like this:

NAME SALARY
A 1000
B 2000
C 3000
D 3000

Now using rank() to get top 3 rows will return
D 3000
C 3000
B 2000

whereas using dense_rank() will return all rows
D 3000
C 3000
B 2000
A 1000


Hope this is helpful..........

Apr 13 '07 #7

hetesp

Hi,

Simplest way is:

select * from emp where rownum <= 10 order by rownum asc;

But if you need to compare the information retrieved, use MINUS .. you may use:

select * from emp e ,( select empno,rownum num from emp order by 1 asc) x
where E.EMPNO = x.empno
and x.num <= 10;


Hope it helps.

Cheers,
Paul

Feb 19 '12 #8