Top 10 Rows

[parag100]

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

[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..............

[masdi2t]

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

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)

[Sandya]

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

select top 10 column name from table name

[Sandya]

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

/* 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

[Sandya]

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

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

[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..........

[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