Oracle

Sql query to get max 5 values

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

Thread Solved

•

Join Date: Feb 2008

Posts: 12

Reputation: iceman29 is an unknown quantity at this point

Solved Threads: 2

iceman29

Offline

Newbie Poster

Sql query to get max 5 values

Jun 5th, 2008

Hi ,

Can we have an sql query to get the max 5 values from a column?
Please paste a sample code if its possible.

Thanks in advance.

Learn to appreciate and appreciate to learn.

•

Join Date: Feb 2008

Posts: 12

Reputation: iceman29 is an unknown quantity at this point

Solved Threads: 2

iceman29

Offline

Newbie Poster

Re: Sql query to get max 5 values

Jun 5th, 2008

Got the soln, as below

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM (SELECT a FROM <tbl> ORDER BY a DESC) WHERE rownum<=5;
select * from (select a from <tbl> order by a DESC) where rownum<=5;

Thanks.

Last edited by peter_budo; Jul 13th, 2008 at 4:44 am. Reason: Keep It Organized - please use [code] tags

Learn to appreciate and appreciate to learn.

•

Join Date: Jun 2008

Posts: 7

Reputation: sqlstarz is an unknown quantity at this point

Solved Threads: 0

sqlstarz

Offline

Newbie Poster

Re: Sql query to get max 5 values

Jul 4th, 2008

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM <tab> WHERE rownum <=5
ORDER BY <a> desc.
select * from <tab> where rownum <=5
order by <a> desc.

I dont think extra subquery is needed here.
pls correct me if m wrong.

Last edited by peter_budo; Jul 13th, 2008 at 4:45 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Feb 2008

Posts: 12

Reputation: iceman29 is an unknown quantity at this point

Solved Threads: 2

iceman29

Offline

Newbie Poster

Re: Sql query to get max 5 values

Jul 12th, 2008

Yes buddy, the extra sub querry is needed, as in below case :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM <tab> WHERE rownum <=5
ORDER BY <a> desc
select * from <tab> where rownum <=5
order by <a> desc

the first 5 rown are getting sorted and then ordered, which would not essesntaily return max 5 values.

However if we use it as :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM (SELECT a FROM <tbl> ORDER BY a DESC) WHERE rownum<=5;
select * from (select a from <tbl> order by a DESC) where rownum<=5;