suresh64633 Posted November 2, 2009 Share Posted November 2, 2009 Hi Guys How do i get 2nd highest values(say salary) without using Sub-query or Limit? Many Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/ Share on other sites More sharing options...
corbin Posted November 2, 2009 Share Posted November 2, 2009 Without limit, it's not possible unless you don't mind pulling all of the results. Why can't you use limit? Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/#findComment-949163 Share on other sites More sharing options...
suresh64633 Posted November 2, 2009 Author Share Posted November 2, 2009 Hi guys I got the query # OPTION ONE: To find 3rd highest select * from salary e where 3 =(select count(distinct salary) from salary where e.salary<=salary) # OPTION TWO: It find only 2nd highest. SELECT max( p1.sal ) FROM jos_salary p1, jos_salary p2 WHERE p1.sal < p2.sal Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/#findComment-949196 Share on other sites More sharing options...
Daniel0 Posted November 2, 2009 Share Posted November 2, 2009 SELECT sal FROM jos_salary ORDER BY sal DESC LIMIT 1,1; Order descendingly, limit to 1 and offset by 1. I believe that should be the second highest. Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/#findComment-949213 Share on other sites More sharing options...
Mchl Posted November 2, 2009 Share Posted November 2, 2009 SELECT max( p1.sal ) FROM jos_salary p1, jos_salary p2 WHERE p1.sal < p2.sal I like this one! 2nd highest value without using Sub-query or Limit Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/#findComment-949262 Share on other sites More sharing options...
Daniel0 Posted November 2, 2009 Share Posted November 2, 2009 Though of course it'll be less efficient than just using a limit. Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/#findComment-949265 Share on other sites More sharing options...
Mchl Posted November 2, 2009 Share Posted November 2, 2009 Of course. But interesting as a puzzle. Quote Link to comment https://forums.phpfreaks.com/topic/179926-how-to-retrieve-second-max-salary-without-using-subquery-and-limit-in-mysql/#findComment-949267 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.