CODEWITHSUNDEEP
mysqlsqldatabase
Luv
Luv

Reputation: 5451

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

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?

Upvotes: 4

Views: 16073

Answers (8)

Tarun Rawat
Tarun Rawat

Reputation: 254

`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

id Salary
1  1200
2  12345
3  123456
4  2535436

Output will be

id salary
4  2535436
2  123456

Upvotes: 0

Syed Yawar
Syed Yawar

Reputation: 113

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.

Upvotes: 0

Pankaj katiyar
Pankaj katiyar

Reputation: 464

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

Upvotes: 0

user2350640
user2350640

Reputation: 37

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

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53830

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

Upvotes: 1

Adam Houldsworth
Adam Houldsworth

Reputation: 64477

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

Upvotes: 4

user928558
user928558

Reputation:

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

Upvotes: 1

Sachin Kainth
Sachin Kainth

Reputation: 46740

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
)

Upvotes: 0

Related Questions