CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
user3752338
user3752338

Reputation:

MYSQL query to find the all employees with nth highest salary

The two tables are salary_employee and employee

employee_salary

salary_id  emp_id  salary

Employee

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

Query to get the all employees who have 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 any better and optimized way we can query this,

2) Is using LIMIT an good option

3) We have more options to calculate the nth highest salary, which is the best and what to follow and when?

One option using : 

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

Using Rank 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

Upvotes: 4

Views: 7681

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You have asked what seems like a reasonable question. There are different ways of doing things in SQL and sometimes some methods are better than others. The ranking problem is just one of many, many examples. The "answer" to your question is that, in general, order by is going to perform better than group by in MySQL. Although even that depends on the particular data and what you consider to be "better".

The specific issues with the question are that you have three different queries that return three different things.

The first returns all employees with a "dense rank" that is the same. That terminology is use purposely because it corresponds to the ANSI dense_rank() function which MySQL does not support. So, if your salaries are 100, 100, and 10, it will return two rows with a ranking of 1 and one with a ranking of 2.

The second returns different results if there are ties. If the salaries are 100, 100, 10, this version will return no rows with a ranking of 1, two rows with a ranking of 2, and one row with a ranking of 3.

The third returns an entirely different result set, which is just the salaries and the ranking of the salaries.

My comment was directed at trying the queries on your data. In fact, you should decide what you actually want, both from a functional and a performance perspective.

Upvotes: 3

Bade Lal
Bade Lal

Reputation: 101

Suppose emp_salary table have the below records:

Employee salary table

And you want to select all employees with nth (N=1,2,3 etc.) highest/lowest (only change >(for highest), < (for lowest) operator according to your needs) salary, use the below 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 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 
                 );

2nd highest salary list

But if you want to display only second highest salary(only single record), use the 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;

2nd-highest-salary(single record-not-duplicate)

Upvotes: 0

Rakesh Singh
Rakesh Singh

Reputation: 1260

try this,

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

Upvotes: 0

dube
dube

Reputation: 5019

LIMIT requires the SQL to skim through all records between 0 and N and therefore requires increasing time the further back in your ranking you want to look. However, IMO that problem cannot be solved better.

  • As Gordon Linoff suggested: Run your option against your data set, using the commonly used ranks (which ranks are queried often, which are not? The result might be fast on rank 1 but terrible on rank 100).

  • Execute and analyze the Query Execution Plan and create indexes accordingly (for example on the salary column) and retest your queries.

Other options:

Option 4:

You could build a ranking table whichs serves as cache. The execution plan of your Limit-Query shows (see sqlfiddle here), that mysql already does create a temporary table to solve the query.

Pros: Easy and fast Cons: Forces you to regenerate the ranking table each time the data changes

Option 5:

You could reconsider how you define "ranks". If we have the following salaries:

  1. 100'000
  2. 100'000
  3. 80'000

Is the employee Nr 3 considered to be of rank 3 or 2? Are 1 and 2 on the same rank (rank 1), but 3 is on rank 3?

If you define rank = order, you can greatly simplify the query to

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

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

Upvotes: 1

Related Questions