I want to find the person with the 22nd highest salary
Use LIMIT
by specifying both the offset and the number of lines.
To get the 22nd person in the highest pay order, do:
SELECT person
FROM employee
ORDER BY salary DESC
LIMIT 21, 1
Note the usage 21
here. This is because the starting row offset (1st highest salary) is actually 0. So the 22nd highest salary would actually be offset 21 (21st row in the count based on 0 or "skip 21 rows") ...
To get the person (s) with the 22nd highest salary, you'll need another level of indirection. Try:
SELECT person
FROM employee
WHERE salary = (
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 21, 1
)
source to share
here's a different one, given that you have a duplicate salary number. I think the limit won't fix the solution to your case if you have 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
believe that you have such records,
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 ║
╚═══════╩════════╝
source to share
Just answered a similar question: select all rows except the last four
In your case, you want to set LIMIT to 1 and OFFSET to position 22.
source to share