Finding the 3rd highest salary in mysql without limit

Question

I am attempting to get the 3rd highest salary of a table without using limit.

In the case of 2nd highest salary I use

SELECT salary FROM table WHERE salary < ( SELECT MAX( salary ) order by salary desc

To get the 3rd highest with the limit I use

select salary from one order by salary desc limit 3,1

So how can I get the nth salary without using limit?

Answer 1

You can try the following query:

   SELECT *
   FROM one one1
   WHERE ( 3 ) = ( SELECT COUNT( one2.salary )
                   FROM one one2
                   WHERE one2.salary >= one1.salary
                 )

Here WHERE ( n ) you can place any number to return that highest salary.

Want to learn more concepts related to SQL? Join this SQL Course by Intellipaat.