Nov 11
Here’s a question i was just posed…
You have a table, TBL_Salary with column names, ID, Name and Salary - you need to write a SQL statement to retrieve the name of the 2nd highest paid salary. Thoughts? Now that i’ve thought about it more i’ve got an answer but i’d like to see what you all think.
November 11th, 2004 at 11:48 am
If you have to do it in one statement:
select top 1 name from TBL_Salary
where id in (
select top 2 id from TBL_Salary
order by salary desc
)
order by salary asc
November 11th, 2004 at 12:31 pm
there could be more then one person that makes this salary:
select *
from tbl_salary
where salary =
(select max(salary)
from tbl_salary
where salary <
(select max(salary)
from tbl_salary)
)
do I win a prize?
November 11th, 2004 at 12:44 pm
What if two people earn the top salary?
John - $330,000
Patrick - $475,000
Ed - $475,000
Steve - $330,000
In this case is the correct answer (Patrick or Ed), (Patrick and Ed), (John or Steve), or (John and Steve)?
November 11th, 2004 at 12:53 pm
SQL SERVER:
SELECT TOP 1 name FROM TBL_Salary WHERE salary < MAX( SALARY ) ORDER BY salary DESC;
MySQL:
SELECT name FROM TBL_Salary ORDER BY salary DESC LIMIT( 1, 1 )
The MySql query is actually better, in some ways, as it will get the second highest salary, even if it is EQUAL to the highest salary. Though that might be against the requirements. So, if your requirements are the second person with the highest salary, the mysql query makes more sense (note that you could use the same query as the SQL Server query, just removing TOP 1 and adding LIMIT 0, 1 to the end). But as soon as you’re talking about multiple people with the same salaries, and how it reacts to that, you need to figure out secondary sorting, or if you want to return a table of everyone with the second highest salary, etc, etc.
November 11th, 2004 at 12:54 pm
Or is the correct answer in the above case NULL?
If this were the Olympics, Ed and Patrick would get the gold. John and Steve would get the bronze. Nobody gets the silver. No one earns the second highest salary.
November 11th, 2004 at 12:56 pm
In short, the requirements are lacking.
November 11th, 2004 at 1:21 pm
If you are using Oracle, just make use of the RANK and PARTION functions. Then all you need is rank = 2 to get any and all persons at teh second highest salary level.
November 11th, 2004 at 2:27 pm
"answers on a postcard…" shouldn’t it be "Answers in a Blog Comment…"
November 11th, 2004 at 2:27 pm
the original question did have a table of salaries, they were unique
Thanks all for the answers!