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