MYSQL query to find all employees with the nth highest salary

Question

Two tables: employee_employee and employee

employee_salary

salary_id  emp_id  salary

Employee

emp_id |    first_name | last_name |    gender  | email | mobile  | dept_id  | is_active

A request to get all employees who have the nth highest salary, where n = 1,2,3, ... any integer

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
                   )

My questions:

1) Is there a better and optimized way that we can request,

---

2) Uses LIMIT is a good option

---

3) We have more opportunities to calculate the nth highest salary, which is the best and what should be observed and when?

One option:

SELECT *
   FROM employee_salary t1
   WHERE ( N ) = ( SELECT COUNT( t2.salary )
                   FROM employee_salary t2 
                   WHERE  t2.salary >=  t1.salary 
                 )

Using the ranking method

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

Answer 1

This is too long for comment.

, . SQL, . - , . "" , order by , group by MySQL. , "".

, , .

" ", . , ANSI dense_rank(), MySQL . , 100, 100 10, 1 1 2.

, . 100, 100, 10, 1, 2 3.

, .

. , , , , .

Answer 2

LIMIT , SQL 0 N , , , . .

• : , ( , ). 1, 100).

• " " (, ) .

:

4:

, . - (. Sqlfiddle ), mysql .

: : ,

5:

, "". :

• 100'000
• 100'000
• 80'000

Nr 3 3 2? 1 2 ( 1), 3 3?

rank = order,

SELECT a.salary, b.first_name FROM employee_salary a, b WHERE a.emp_id = b.emp_id LIMIT 1 OFFSET 4

demo: http://sqlfiddle.com/#!2/e7321d/1/0

Answer 3

,

SELECT * FROM one as A WHERE ( n ) = ( SELECT COUNT(DISTINCT(b.salary)) FROM one as B  WHERE 
B.salary >= A.salary )

Answer 4

, emp_salary :

nth (N = 1,2,3 ..) / ( > ( ), < ( ) ) , sql:

SELECT DISTINCT(a.salary), 
       a.id, 
       a.name 
       FROM emp_salary a 
       WHERE N = (SELECT COUNT( DISTINCT(b.salary)) FROM emp_salary b
                  WHERE b.salary >= a.salary 
                 );

For example, if you want to select all employees with the 2nd highest salary, use below sql:

 SELECT DISTINCT(a.salary), 
       a.id, 
       a.name 
       FROM emp_salary a 
       WHERE 2 = (SELECT COUNT( DISTINCT(b.salary)) FROM emp_salary b
                  WHERE b.salary >= a.salary 
                 );

But if you want to display only the second highest salary (only one record), use below sql:

SELECT DISTINCT(a.salary), 
       a.id, 
       a.name 
       FROM emp_salary a 
       WHERE 2 = (SELECT COUNT( DISTINCT(b.salary)) FROM emp_salary b
                  WHERE b.salary >= a.salary 
                 ) limit 1;