Geek Answers Handbook

Maximum employee salary in a special department

EmpID   EmpName EmpSalary   EmpDept
1       Steve    5000       HR
2       Robert   5000       Management
3       Brad     3000       HR
4       Sam      4000       HR
5       Dave     2000       Management
6       Stuvart  4500       Management

How to get data about employees from the EMPLOYEE table, whose salary is maximum, and it belongs to the personnel department ... My request

SELECT EmpID,EmpName,EmpSalary,EmpDept 
FROM EMPLOYEE 
WHERE EmpSalary IN (select max(EmpSalary) from EMPLOYEE) 
AND EMPDEPT='HR'

I tried the query, it gives me the exact result, but due to a performance issue, I cannot use internal queries.

+4
source share
5 answers

You can use the clause order bywith rownumfor version of Oracle <12s:

SELECT EmpID, EmpName, EmpSalary, EmpDept 
FROM EMPLOYEE 
WHERE ROWNUM = 1 AND EMPDEPT = 'HR'
ORDER BY EmpSalary DESC

Otherwise, you can use the following:

SELECT EmpID, EmpName, EmpSalary, EmpDept 
FROM EMPLOYEE 
WHERE EMPDEPT = 'HR'
ORDER BY EmpSalary DESC
FETCH FIRST ROW WITH TIES
Option

PS: with ties ( , rownum, ).

+2
SELECT EmpID,EmpName,MAX(EmpSalary),EmpDept
FROM Employee
WHERE EmpDept='HR'
GROUP BY EmpSalary
+1

.

SELECT Max(EmpSalary)
FROM   employees
WHERE  EmpDept = 'HR';

EmpSalary, . , HR. EmpDept. - , , , .

0

. , .

select emp.*
from employee emp
inner join (select EmpDept, max(salary) ms from employee group by EmpDept) m
  on emp.EmpDept = "HR" and emp.salary = m.ms
0
source

One of the ways Oracle 11g works (as you noted your question), not mentioned in other answers, is an analyticmax() function. It allows the query to see values ​​in other rows without losing details from the current row. For the human resources department, these are:

select EmpID, EmpName, EmpSalary, EmpDept 
  from (select EmpID, EmpName, EmpSalary, EmpDept, 
               max(EmpSalary) over () as max_sal
          from employee where empdept = 'HR')
  where EmpSalary = max_sal

 EMPID EMPNAME    EMPSALARY EMPDEPT
------ ---------- --------- ----------
     1 Steve           5000 HR

A similar query shows all employees with the maximum salary in their departments:

select EmpID, EmpName, EmpSalary, EmpDept 
  from (select EmpID, EmpName, EmpSalary, EmpDept, 
               max(EmpSalary) over (partition by EmpDept) as max_sal
          from employee)
  where EmpSalary = max_sal

 EMPID EMPNAME    EMPSALARY EMPDEPT
------ ---------- --------- ----------
     1 Steve           5000 HR
     2 Robert          5000 Management
0
source

More articles:


All Articles