I want to find the person with the 22nd highest salary

Question

I am using MySQL, I have 50 records in employee table. I want to find the person with the 22nd highest salary.

Why is the Close link missing on this question? Tons of dupes... — Danny Beckett, Feb 06 '13 at 07:06

lc. · Accepted Answer · 2013-02-06T08:49:59.377

5

Use LIMIT, specifying both an offset and a row count.

To get the 22nd ranked person in order of highest salary, do:

SELECT person
FROM employee
ORDER BY salary DESC
LIMIT 21, 1

Notice the use of 21 here. This is because the offset of the initial row (1st highest salary) is actually 0. Therefore the 22nd highest salary will actually be an offset of 21 (the 21st row in 0-based counting, or "skip 21 rows").

To get the person(s) with the 22nd highest salary, you will need one more level of indirection. Try:

SELECT person
FROM employee
WHERE salary = (
    SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 21, 1
)

edited Feb 06 '13 at 08:49

answered Feb 06 '13 at 06:58

lc.

113,939
20
158
187

So no two employees are on the same salary? Really? – Strawberry Feb 06 '13 at 08:21
@Strawberry Good point. I didn't even think about that. Edited. – lc. Feb 06 '13 at 08:50
Good - except I think your offset logic is flawed. – Strawberry Feb 06 '13 at 09:24
Because I'm a numpty - lc, that's spot on. – Strawberry Feb 06 '13 at 15:10
thank you so much for helping me on this. it worked for me. great efforts – Gopal Oct 11 '18 at 19:43

John Woo · Answer 2 · 2013-02-06T07:28:41.303

here's another one, considering you have duplicate salary number. I guess limit won't correct solve your case if you have some duplicates. Try something like this,

SELECT aa.*
FROM   table1 aa
       INNER JOIN
        (
          SELECT @row:=@row+1 rankNo,
                 a.Salary
          FROM   (SELECT DISTINCT Salary FROM table1) a, 
                 (SELECT @row:=0) s
          ORDER  BY Salary DESC
        ) bb ON aa.Salary = bb.Salary AND
                bb.rankNo = 2

SQLFiddle Demo

consider you have records like this,

CREATE TABLE Table1
    (`EmpID` int, `Salary` int);

INSERT INTO Table1
    (`EmpID`, `Salary`)
VALUES
    (1, 10),
    (2, 12),  -- duplicate
    (3, 11),
    (4, 12),  -- duplicate
    (5, 14),
    (6, 12);  -- duplicate

╔═══════╦════════╗
║ EMPID ║ SALARY ║
╠═══════╬════════╣
║     1 ║     10 ║
║     2 ║     12 ║
║     3 ║     11 ║  -- you want to get this value (*2nd from the last value*)
║     4 ║     12 ║
║     5 ║     14 ║
║     6 ║     12 ║
╚═══════╩════════╝

score 1 · Answer 3 · edited Apr 03 '19 at 18:34

1

SELECT MIN(salary) FROM (
    SELECT DISTINCT salary  FROM employee ORDER BY salary DESC LIMIT 22
) limited_salary

edited Apr 03 '19 at 18:34

Andy Hoffner

3,267
2
21
22

answered Apr 03 '19 at 18:29

Kinan

11
2

score 0 · Answer 4 · edited May 23 '17 at 12:12

0

Just answered a similar question here: select all rows except the four most recent

In your case, you'll want to LIMIT to 1, and OFFSET to the 22 position.

edited May 23 '17 at 12:12

Community

1
1

answered Feb 06 '13 at 06:58

fionbio

3,368
2
23
38

score 0 · Answer 5 · edited May 23 '17 at 10:32

0

LIMIT 21,1

How to find n'th highest value of a column?

Query to find nth max value of a column

edited May 23 '17 at 10:32

Community

1
1

answered Feb 06 '13 at 06:59

Danny Beckett

20,529
24
107
134

I want to find the person with the 22nd highest salary

5 Answers5