How to Get Depatment Wise Max Salary as Well as Name of Employee Having It

How to get depatment wise max salary as well as name of employee having it?

Here is a correct solution:

SELECT concat(e.First_name, ' ', e.Last_name) as Name, e.SALARY
FROM Employee e
WHERE salary = (SELECT MAX(SALARY) FROM Employee e2 WHERE e2.Department = e.Department);

Group by clause to get name of highest paid employee

SQL Server 2008 supports Window Functions which help you get what you want.

WITH recordList
AS
(
    SELECT  dept, employeeName, salary,
            DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) rn
    FROM    employee
)
SELECT dept, employeeName, salary
FROM   recordList
WHERE  rn = 1

• SQLFiddle Demo
• TSQL Ranking Function

Find max and second max salary for a employee table MySQL

You can just run 2 queries as inner queries to return 2 columns:

select
  (SELECT MAX(Salary) FROM Employee) maxsalary,
  (SELECT MAX(Salary) FROM Employee
  WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as [2nd_max_salary]

SQL Fiddle Demo

select department(s) with maximum number of employees

Just a little more verbose than the other two solutions, but it will get the job done...feel free to tweak to your convenience.

select countbydept.*
from
(
  -- from EMP table, let's count number of records per dept
  -- and then sort it by count (highest to lowest)
  -- and take just the first value. We just care about the highest
  -- count
  select dept_id, count(*) as counter
  from emp
  group by dept_id
  order by counter desc
  limit 1
) as maxcount

inner join

(
  -- let's repeat the exercise, but this time let's join
  -- EMP and DEPT tables to get a full list of dept and 
  -- employe count
  select
    dept.id,
    dept.`name`,
    count(*) as numberofemployees
  from dept
  inner join emp on emp.dept_id = dept.id
  group by dept.id, dept.`name`
) countbydept 

-- combine the two queries's results by matching the employee count
on countbydept.numberofemployees = maxcount.counter

Example: http://sqlfiddle.com/#!9/7d6a2d/1

---

Related Topics