Find the top 2 employees who have the highest salary without using top
database, mysql, sql
Solution
I believe this interview question is trying to direct you to nested selects, or common table expressions, or something of the sort. `TOP 2` is the easy answer and obviously `TOP` was implemented for just this purpose - the interview wants you to do it "manually".
In theory-code. Give each row a row count on the first (nested) select, then select from the results of that where the row count is less than one more than the number of rows you need, 3 in this case.
MySQL - Get row number on select
Nested select (pseudo-code):
select row_count, * from salary order by salary desc
Outer select:
select * from <nested select> where row_count < 3
I'm sorry this isn't MySQL code, but I only know SQL Server.
I have some SQL Server code that works using the row count:
declare @Salaries table
(
id int,
salary money
)
insert into @salaries (id, salary)
values (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4) -- A duplicating salary
;WITH Props AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY salary desc) AS RowNumber
FROM @Salaries
)
SELECT * FROM Props WHERE RowNumber < 3
This returns rows with ID 4 and 5.
Tackling Sachin Kainth's answer
I believe this answer is incorrect. Try the following SQL Server code:
declare @Salaries table
(
id int,
salary money
)
insert into @salaries (id, salary)
values (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4)
select * from @salaries where salary in -- "in" introduces the problem
(
SELECT MAX(E1.Salary)
FROM @salaries E1, @salaries E2
WHERE E1.Salary < E2.Salary
union
SELECT MAX(Salary)
FROM @salaries
)
This returns rows with ID 3, 4 and 5. Instead of just 4 and 5. This is because the outer select with clause `where salary in` will take rows 3, 4 and 5, which all have their salary being returned by the nested select (which returns salary values 3 and 4).
Problem
Find the top 2 employees who have the highest salary. Table name is `salary`, columns are `name,salary` We can do this query by using limit command as ``` select * from salary order by salary DESC limit 0,2 ``` But how to do this without using top and limit?