By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,627 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,627 IT Pros & Developers. It's quick & easy.

Find second highest one in a table without subquery

P: 6
I want second highest value in a table without using subquery.

please send this answer .It will be very helpfull.
Nov 10 '06 #1
Share this Question
Share on Google+
4 Replies


ronverdonk
Expert 2.5K+
P: 4,258
Highest value of what???

In your select do an ORDER BY colum_name DESC LIMIT 1 and you'll get the 1 row with the highes value.

Ronald :cool:
Nov 10 '06 #2

P: 10
I want second highest value in a table without using subquery.

please send this answer .It will be very helpfull.
Hi,

The solution I knows it
if U have a column sal in a table emp want to find out the second highest value the solution is

select sal from emp a where 2=(select count(distinct(sal)) from emp b where a.sal<=b.sal);
May be it helpfull to you.
Nov 27 '06 #3

ronverdonk
Expert 2.5K+
P: 4,258
The only one I can come up with is:
Expand|Select|Wrap|Line Numbers
  1. SELECT MIN(field) 
  2.        FROM (SELECT field 
  3.              FROM table 
  4.              ORDER BY field DESC LIMIT 2 ) 
  5.        AS result;
Ronald :cool:
Nov 27 '06 #4

miller
Expert 100+
P: 1,089
To get the second highest value, simply take advantage of ORDER BY and LIMIT with offets.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM yourTable ORDER BY yourField DESC LIMIT 1,1;
  2.  
or for the fourth highest value:


Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM yourTable ORDER BY yourField DESC LIMIT 3,1;
  2.  
Nov 28 '06 #5

Post your reply

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