Oracle

how to display only the first 3 rows

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Mar 2009

Posts: 27

Reputation: achiman is an unknown quantity at this point

Solved Threads: 0

achiman

Offline

Light Poster

how to display only the first 3 rows

Mar 27th, 2009

Please how can I write a query to display only the top three earners in a table in desc order e.g
Emp Sal
John 100
Jam 120
Abi 90
Jo 170
the query should display
Emp Sal
John 200
Jam 170

thanks

•

Join Date: Mar 2009

Posts: 27

Reputation: achiman is an unknown quantity at this point

Solved Threads: 0

achiman

Offline

Light Poster

Re: how to display only the first 3 rows

Mar 27th, 2009

I have tried using numrow but it doesnt do the job

•

Join Date: Mar 2009

Posts: 27

Reputation: achiman is an unknown quantity at this point

Solved Threads: 0

achiman

Offline

Light Poster

Re: how to display only the first 3 rows

Mar 27th, 2009

please is there any function called rank in oracle and how can i use it

•

Join Date: Mar 2009

Posts: 27

Reputation: achiman is an unknown quantity at this point

Solved Threads: 0

achiman

Offline

Light Poster

Re: how to display only the first 3 rows

Mar 27th, 2009

rank does not work maybe because im using oracle8i

•

Join Date: Feb 2007

Posts: 2,144

Reputation: debasisdas will become famous soon enough

Solved Threads: 132

debasisdas debasisdas is offline

Offline

Postaholic

Re: how to display only the first 3 rows

Mar 30th, 2009

Try this

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM emp WHERE rownum<4 ORDER BY sal desc
select * from emp where rownum<4 order by sal desc

Share your Knowledge.

•

Join Date: May 2009

Posts: 10

Reputation: manuhar is an unknown quantity at this point

Solved Threads: 0

manuhar

Offline

Newbie Poster

Re: how to display only the first 3 rows

May 31st, 2009

ya what debasisdas wrote works for ur query

•

Join Date: Jun 2009

Posts: 13

Reputation: anubina is an unknown quantity at this point

Solved Threads: 1

anubina

Offline

Newbie Poster

Re: how to display only the first 3 rows

Jun 18th, 2009

•

Originally Posted by debasisdas

Try this
Help with Code Tags

sql Syntax (Toggle Plain Text)
SELECT * FROM emp WHERE rownum<4 ORDER BY sal desc
select * from emp where rownum<4 order by sal desc

Hello,

Before solution is incorrect.

The good solution:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT *
   FROM (SELECT *
                 FROM emp
               ORDER BY sal DESC)
 WHERE ROWNUM <= 3
SELECT *
   FROM (SELECT *
                 FROM emp
               ORDER BY sal DESC)
 WHERE ROWNUM <= 3

First step - Obtain the records with all fields in the correct order (view-inline)
Second step - Cut the records in the 3 (Top1-query)

Last edited by peter_budo; Jun 19th, 2009 at 1:05 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.