Find the top 2 employees who have the highest salary without using top

Question

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?

It a interview question, in which we can't use top and limit. — Luv, May 23 '12 at 14:34
You should have disclosed that this is an interview question. — Bernard, May 23 '12 at 14:35
@Keyser Are there good ones? All I've dealt with are "gotchas" and other BS which don't measure anything except to show how so-called "clever" the interviewer is. — N West, May 23 '12 at 14:40
@Keyser Since when is SO an interview question quality control system and since when do we close questions because it filters out certain solutions? — Adam Houldsworth, May 23 '12 at 14:40
@AdamHouldsworth You're confusing me with "spender". I'm not closing anything. Also, SO **is** an interview question quality control system, among other things. — keyser, May 23 '12 at 14:42
@Keyser Sorry I wasn't very explicit with that part, apologies. But in this case the person is presumably the recipient of the interview question and therefore has no power to change it. Calling it out as bad is pointless. And SO is a Q&A site, if the question was "is this interview question bad", surely it would qualify as opinionated or without a clear answer and get closed? — Adam Houldsworth, May 23 '12 at 14:43
@NWest I'd say there are good ones, yes. Something that requires problem solving, but doesn't require you to know some magic SQL keyword. The easiest example would be some algorithm with multiple good solutions. — keyser, May 23 '12 at 14:43
@AdamHouldsworth It would be pointless to do so in an answer, but not in a comment. They're allowed to be 50% "pointless" (or should be :p). — keyser, May 23 '12 at 14:46

score 4 · Accepted Answer · edited May 23 '17 at 12:29

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).

This is the only "interview answer" as it doesn't rely on any magic keywords. I'd +1 if I had any votes left — keyser, May 23 '12 at 14:52
I'll +1 this if you flesh out your MySQL answer so that it works instead of linking to "get row number on select" — D'Arcy Rittich, May 23 '12 at 15:02

score 1 · Answer 2 · answered May 23 '12 at 14:42

According to the SQL:2008 standard you can append FETCH FIRST 10 ROWS ONLY to your query. Although, I've never tried this. So in your case you would have

SELECT * FROM salary ORDER BY salary DESC FETCH FIRST 2 ROWS ONLY

Marcus Adams · Answer 3 · 2012-05-23T15:51:30.780

Here it is in MySQL:

SET @row := 0;
SELECT name, salary FROM
(SELECT name, salary, @row := @row + 1 AS Row FROM salary ORDER BY salary DESC)
  AS derived1
WHERE Row < 3

There's still a caveat. If there are duplicate salaries, the results may be skewed. Ties won't be included in the results if the result set is greater than two rows, yet since the question is for the two employees with the highest salary and not the employees with the two highest salaries, this is the best I can do.

Maybe the correct answer is to ask, "What should I do in the case of duplicate salaries?"

Here's the trick if it absolutely has to be a single query:

SELECT name, salary FROM
(SELECT name, salary, @row := @row + 1 AS Row FROM (SELECT @row := 0) AS d1, salary)
  AS d2
WHERE Row < 3

That should work fine with duplicate salaries as they will occupy the top two rows from your order by, the row number is then responsible for selecting just the two. +1 for the MySQL sample, I could only muster a SQL Server sample ;-) — Adam Houldsworth, May 23 '12 at 15:44

score 0 · Answer 4 · answered May 23 '12 at 14:39

0

select * from salary where salary in
(
SELECT MAX(E1.Salary)  
FROM Salary E1, Salary E2
WHERE E1.Salary < E2.Salary

union

SELECT MAX(Salary)  
FROM Salary
)

answered May 23 '12 at 14:39

Sachin Kainth

45,256
81
201
304

This one fetches the top 2? Could you explain? I'm intrigued. – keyser May 23 '12 at 14:45
@Keyser The first part selects the MAX where it is less than the salary, which should land on the 2nd because the 1st isn't less than anything, then you union it with the 1st. Wonder what happens when the value of the top two salaries are the same :-) – Adam Houldsworth May 23 '12 at 14:48
Nice. Don't forget to get the names for those salaries. – Marcus Adams May 23 '12 at 14:49
@MarcusAdams Just assume the first is the CEO and the second is the CFO lol – Adam Houldsworth May 23 '12 at 14:50
Well, I can only answer for SQL Server. I'm not sure if this works in MySQL. The first inner select gets the second highest salary. I union that with the highest salary (the second select) and then I select everything in the table that has those two salaries. – Sachin Kainth May 23 '12 at 14:50
@SachinKainth I cannot see that working if the top two salaries are the same. – Adam Houldsworth May 23 '12 at 14:51
-1 This does not find employees, and gives invorrect results if top two salaries are the same. – D'Arcy Rittich May 23 '12 at 14:51
@AdamHouldsworth it does because of SELECT MAX(E1.Salary) FROM Salary E1, Salary E2 WHERE E1.Salary < E2.Salary – Sachin Kainth May 23 '12 at 14:52
if you have doubts about this then please do try it - it does work. – Sachin Kainth May 23 '12 at 14:52
@SachinKainth Exactly... if E1.Salary = E2.Salary then it is not less than - you get an indeterminate record back - in my books that means "doesn't work". OK I'll try it. – Adam Houldsworth May 23 '12 at 14:52
@SachinKainth No, doesn't work if the top two are the same, returns 3 records instead of 2. It is because of the very top line `where salary in`, the salary that duplicates hits the `in` twice and thus extra records slip through. – Adam Houldsworth May 23 '12 at 14:57
@AdamHouldsworth I believe sir you are wrong. The union will ensure that distinct rows are returned. – Sachin Kainth May 23 '12 at 15:01
@SachinKainth I've amended my answer to show you what I did. The distinct action of the union is on the nested select and likely correctly returns 2 rows; the outer select is the problem. – Adam Houldsworth May 23 '12 at 15:03

score 0 · Answer 5 · edited Sep 05 '13 at 07:54

0

+------+
| Sal  |
+------+
| 3500 | 
| 2500 |
| 2500 | 
| 5500 |
| 7500 |
+------+

The following query will return the Nth Maximum element.

select SAL from EMPLOYEE E1 where 
 (N - 1) = (select count(distinct(SAL)) 
            from EMPLOYEE E2 
            where E2.SAL > E1.SAL )

edited Sep 05 '13 at 07:54

Noel

10,152
30
45
67

answered Sep 05 '13 at 07:37

user2350640

37
3

score 0 · Answer 6 · answered May 05 '15 at 11:39

table structure

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `salary` int(10) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Mysql Query for nth term n represent nth number of item

SELECT salary 
FROM emp 
WHERE salary = (SELECT DISTINCT(salary) 
                FROM emp AS e1 
                WHERE (n) = (SELECT COUNT(DISTINCT(salary)) 
                             FROM emp AS e2 
                             WHERE e1.salary <= e2.salary))

score 0 · Answer 7 · edited Jun 10 '17 at 00:44

SELECT e1.EmployeeID, e1.LastName, COUNT(DISTINCT e2.EmployeeID) AS sals_higher
FROM Employees e1
INNER JOIN Employees e2 ON e1.EmployeeID < e2.EmployeeID
GROUP BY e1.EmployeeID
HAVING sals_higher <= 2
ORDER BY e1.EmployeeID DESC

Visit https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union3 and try the given piece of code, it gives you top 2 max EmployeeID.

score 0 · Answer 8 · answered Mar 22 '18 at 18:39

`I think this might work

    select * from salary s1 where 2>=(select count(distinct id) from salary s2
 where s1.salary<=s2.salary) order by salary desc;

This is co-related query.For every row in outer query,inner query will run and will return a count value of by comparing salary value of from outer query to every salary in table . It is like a nested loop of programming language

Say we have table Salary with two column id and salary

Output will be

id salary
4  2535436
2  123456

Find the top 2 employees who have the highest salary without using top

8 Answers8