title: MYSQL query to find the all employees with nth highest salary
body: <p>The two tables are salary_employee and employee</p>
  
  <p><strong>employee_salary</strong></p>
  
  <pre><code>salary_id  emp_id  salary
  </code></pre>
  
  <p><strong>Employee</strong></p>
  
  <pre><code>emp_id |    first_name | last_name |    gender  | email | mobile  | dept_id  | is_active
  </code></pre>
  
  <p>Query to get the all employees who have nth highest salary where n =1,2,3,... any integer   </p>
  
  <pre><code>SELECT  a.salary, b.first_name 
  FROM    employee_salary a 
  JOIN    employee b 
  ON      a.emp_id = b.emp_id 
  WHERE   a.salary = (
                        SELECT    salary 
                        FROM      employee_salary  
                        GROUP BY  salary 
                        DESC     
                        LIMIT 1 OFFSET N-1
                     )
  </code></pre>
  
  <p><strong>My Questions:</strong> </p>
  
  <p>1) Is there any better and optimized way we can query this, </p>
  
  <hr>
  
  <p>2) Is using LIMIT an good option</p>
  
  <hr>
  
  <p>3) We have more options to calculate the nth highest salary, which is the best and what to follow and when? </p>
  
  <p>One option using : </p>
  
  <pre><code>SELECT *
     FROM employee_salary t1
     WHERE ( N ) = ( SELECT COUNT( t2.salary )
                     FROM employee_salary t2 
                     WHERE  t2.salary &gt;=  t1.salary 
                   )
  </code></pre>
  
  <p><strong>Using Rank Method</strong></p>
  
  <pre><code>SELECT salary
  FROM
  (
    SELECT @rn := @rn + 1 rn,
         a.salary
    FROM tableName a, (SELECT @rn := 0) b
    GROUP BY salary DESC
  ) sub
  WHERE sub.rn = N
  </code></pre>