CODEWITHSUNDEEP
sql
avi cohen
avi cohen

Reputation: 1

Names of employees working in the department where the employee receives the high salary

I'm new to SQL, and I'm trying to get names of employees working in the department where the employee receives the highest salary.

I succeeded to return the employee with the highest salary, but now I want to perform union with their Dno and the rest of the employee's row, how I can do this?

SELECT DISTINCT Fname, Lname, salary
FROM employee 
WHERE Dno = (SELECT Dno FROM employee WHERE MAX(salary));

/*names of employees working in the department where the employee receives the high salary

Upvotes: 0

Views: 147

Answers (3)

Rion Williams
Rion Williams

Reputation: 76547

If you wanted a list of all of the employees in the same department as the one with the highest salary, you wouldn't need to perform an additional union, as you could just use an ORDER BY on salary and select the department for that highest salary level in a WHERE clause as seen below:

SELECT  Fname,
        Lname,
        salary
FROM    employee
WHERE   Dno = (SELECT TOP 1 Dno FROM employee ORDER BY salary DESC)

Upvotes: 0

Fact
Fact

Reputation: 2440

You can have nested select like this :

Select * from employee where deoptno in 
   ( Select deptno from empoloyee where salary = (Select max(salary) from employee))

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

You are close, but you should be checking the salary in the WHERE clause:

SELECT Fname, Lname, salary
FROM employee 
WHERE salary = (SELECT MAX(salary) FROM employee);

We could also use RANK or ROW_NUMBER here, e.g.

WITH cte AS (
    SELECT Fname, Lname, salary, RANK() OVER (ORDER BY salary DESC) rnk
    FROM employee
)

SELECT Fname, Lname, salary
FROM cte
WHERE rnk = 1;

Upvotes: 3

Related Questions