CareerCup

@above
What if three employees the same highest salary?

@above ^
I think it should work.

SELECT min(salary) FROM 
(SELECT Distinct TOP 3 salary FROM employee WHERE deptid = <INPUT>)

TOP operator eliminates duplicate by default. So, there is no need to use Distinct here.

The absolute right one would be:
SELECT min(salary) FROM
(SELECT Distinct TOP 3 salary FROM employee WHERE deptid = <INPUT>
ORDER BY salary)

Actually the above query is wrong too. Missing order by salary "desc".

Hi I dont know how you are able to write queries without any tables?

I tested them on SQL Server:
select d.DeptName,max(e.SALARY) as SALARY from EMPLOYEE e
inner join DEPARTMENT d on e.DeptID = d.DeptID group by d.DeptName;

select MIN(ta.SALARY) from
(select top 3 SALARY from EMPLOYEE e inner join DEPARTMENT d on
e.DeptID = d.DeptID where d.DeptName='Marketing' order by e.SALARY desc) ta;

select salary,deptid from employee where salary=(select max(salary) from employee where salary<(select max(salary) from employee
where salary<(select max(salary) from employee)))

just tried! Correct me if i am wrong :)

Use Partition by

the above answers did not consider the situation that multiple employees may have the same highest salary. here is my answer tested in the mysql:
SELECT temp.max_salary, ssn, dno from employee
inner join
(select max(salary) as max_salary, dno as dnum
from employee
group by dno) temp
where employee. salary = max_salary and employee.dno= temp.dnum

Second max salary

select max(salary) from employee where salary != (select max(salary) from employee)

Third max salary
select max(salary) from employee where salary <

(select max(salary) from employee where salary != (select max(salary) from employee))

So on...

In MySQL:
1) SELECT d.deptid, max(e.salary) FROM employee e INNER JOIN department d ON e.deptid = d.deptid GROUP BY d.deptid.
2)SELECT salary FROM employee WHERE deptid = <INPUT> GROUP BY salary DESC LIMIT 2,1;

SQL:
SELECT min(salary) FROM
(SELECT TOP 3 salary FROM employee WHERE deptid = <INPUT>)