469,280 Members | 1,816 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,280 developers. It's quick & easy.

Top 10 Rows

how i retrive top or bottom 10 rows in a table in oracle ?
give select statement ...
Apr 12 '07 #1
7 86205
chandu031
78 Expert
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
37
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)
Apr 13 '07 #3
Sandya
7
how i retrive top or bottom 10 rows in a table in oracle ?
give select statement ...

select top 10 column name from table name
Apr 13 '07 #4
Sandya
7
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
Apr 13 '07 #5
Sandya
7
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
Apr 13 '07 #6
chandu031
78 Expert
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
1
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Subba Rao via DotNetMonster.com | last post: by
12 posts views Thread by Graham Blandford | last post: by
68 posts views Thread by Martin Joergensen | last post: by
11 posts views Thread by jimstruckster | last post: by
reply views Thread by rn5a | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.