443,627 Members | 2,339 Online
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
4 Replies

 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

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

 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 SELECT * FROM yourTable ORDER BY yourField DESC LIMIT 1,1;   or for the fourth highest value: Expand|Select|Wrap|Line Numbers SELECT * FROM yourTable ORDER BY yourField DESC LIMIT 3,1;   Nov 28 '06 #5