What is the SQL query to display the second highest salary of an employee?

Question

You may assume that the employee table has a single column named 'salary'.
The first right answer would surely be chosen as the best answer by me. Also suggest a genuine forum or community which answers SQL based queries.

Answer 1

I would use something like this:

select max(salary) from tbl_employees
where salary not in
(select max(salary) from tbl_employees)

Answer 2

You can use a derived table to get the result. As this is ANSI-SQL, its portable between the decent databases and
its a general approach to the problem, you can also use it to
get the third, fourth, ..nth value.

The derived table is the select in parenthesis in the from clause.
You give it a valid name, top2 here, and use it like a normal table.

Get the top 2 records in the derived table, and then get the last of it by reversing the order in the outer select.

SELECT TOP 1 salary
FROM
(SELECT TOP 2 salary
FROM employee
ORDER BY salary ASC) top2
ORDER BY salary DESC

I use usenet groups for any sql questions, there are specialized groups for any sql dialect and the quality of the answered is high

Answer 3

Hmmm...... Correlated subquery. It may be difficult for u to understand the logic but it will definitely work.

Select salary from employee e where 1 = ( select count(*) from employee where e.salary < salary); Syntax is for Oracle. If using other databases chose equivalent syntax. Write me if u dont understand the logic.

Answer 4

Don't think you can pick the 2nd highest directly.
you can get the salaries ordered,
"select ...
from ...
where ..
order by salary"
and you can get the max
"select max(salary) ..."
perhaps you can combine the two?
something like
select max(salary)
from ...
where salary not in (select max(salary)..."

Just some ideas to play with.

Answer 5

SELECT max(salary) as secondHighest
FROM employee
WHERE salary < (
SELECT max(salary)
FROM employee
)

There's lots of places for SQL related forums
http://www.google.ca/search?hl=en&q=sql+forum&meta=