CODEWITHSUNDEEP
mysqlsqlsql-server
saran
saran

Reputation: 1291

select max salary in sql

Emp table

id          ename
----------- ----------
1           apv
2           sug
3           raj
4           ram
5           sam
6           apv1
7           sug1
8           raj1
9           ram1
10          sam1

Dept Table

dept  salary      id
----- ----------- -----------
a     1000        1
b     2000        2
c     3000        3
d     5000        4
e     7000        5
a     20000       6
b     500         7
c     5000        8
a     1000        9
b     90000       10

How can I return both the Max(salary) from each dept and also the details of who earns that salary? (select id,ename,dept,salary)

Upvotes: 0

Views: 7564

Answers (6)

amit
amit

Reputation: 1

SELECT emp.ename,dept.dept,max(dept.salary) from dept left join emp on dept.id=emp.id group by dept.dept

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Prequery by department first, then go back to itself on same department and matching salary. From THAT table, you can tie to the employee table. This version will allow multiple people in the same department with the same salary to be pulled out... ex: 5 people in Dept X earning 75,000.

SELECT
      d1.Dept,
      d1.Salary,
      Emp.id,
      Emp.name
   from 
      ( SELECT
              dept,
              MAX(salary) MaxSal
           from 
              Dept
           group by 
              dept ) ByDept
      join Dept d1
         ON ByDept.dept = d1.dept
         and ByDept.MaxSal = d1.Salary
         join Emp
            ON d1.id = Emp.id

Upvotes: 1

HamoriZ
HamoriZ

Reputation: 2438

select mm, d1.dept, d1.id, ename from DEPT D1,
(select max(salary) mm, dept from DEPT group by dept) D2, EMP 
where D2.mm=d1.salary and d2.dep=d1.dept
EMP.ID=DEPT.ID

Upvotes: 3

mohsensajjadi
mohsensajjadi

Reputation: 335

select dept.dept, max(dept.salary), emp.id, emp.ename
from emp inner join dept on emp.id=dept.id
group by dept.dept, emp.id, emp.ename

should do the trick, just know that if two people in same dept have same salary and it is the maximum salary you will have both people as outout.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @Emp table (id int, ename varchar(4))
declare @Dept table (dept char(1), salary int, id int)

insert into @Emp values
(1,           'apv'),
(2,           'sug'),
(3,           'raj'),
(4,           'ram'),
(5,           'sam'),
(6,           'apv1'),
(7,           'sug1'),
(8,           'raj1'),
(9,           'ram1'),
(10,          'sam1')

insert into @Dept values
('a',     1000,        1),
('b',     2000,        2),
('c',     3000,        3),
('d',     5000,        4),
('e',     7000,        5),
('a',     20000,       6),
('b',     500,         7),
('c',     5000,        8),
('a',     1000,        9),
('b',     90000,       10)

;with cte as
(
  select
    id,
    salary,
    dept,
    rank() over(partition by dept order by salary desc) as rn
  from @Dept
)
select
  e.ename,
  e.id,
  c.salary,
  c.dept
from cte as c
  inner join @Emp as e
    on c.id = e.id
where rn = 1

Result

ename id          salary      dept
----- ----------- ----------- ----
apv1  6           20000       a
sam1  10          90000       b
raj1  8           5000        c
ram   4           5000        d
sam   5           7000        e

Upvotes: 1

hsz
hsz

Reputation: 152216

I do not know if I understand you at all but you can try something like:

   SELECT id, ename, dept, MAX(salary) AS salary
     FROM Dept_Table AS d
LEFT JOIN Emp_Table AS e
       ON e.id = d.id
 GROUP BY dept

Upvotes: 0

Related Questions