I have a table of employees and salaries defined that way:
"name" (type: VARCHAR)
"salary" (type: INTEGER)
What query can I use to get the second highest salary in this table?
解决方案
Here's one that accounts for ties.
Name Salary
Jim 6
Foo 5
Bar 5
Steve 4
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))
Result --> Bar 5, Foo 5
EDIT:
I took Manoj's second post, tweaked it, and made it a little more human readable. To me n-1 is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.
SELECT name,salary FROM employees
WHERE salary = (SELECT DISTINCT(salary) FROM employees as e1
WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM employees as e2
WHERE e1.salary <= e2.salary)) ORDER BY name
Result --> Bar 5, Foo 5