Find Employee Name Who Is Having Highest Salary in Given Month in SQL

SQL query to find out the name and salary of the employee who received highest salary for each month

You did the first step and miss the second. You have found the highest salary per month. Now find the employees earning that much.

Here is one way to do that:

select *
from salary_data s
join emp_data e on e.id = s.id -- s.id is a misnomer for s.emp_id
where (s.month, s.salary) in
(
  select month, max(salary)
  from salary_data
  group by month
);

Here is another (only possible since MySQL 8):

select *
from
(
  select
    id as emp_id, month, salary, 
    max(salary) over (partition by month) as max_salary_for_the_month
  from salary_data
) s
join emp_data e on e.id = s.emp_id
where s.salary = s.max_salary_for_the_month
order by s.month;

Return name of the employee who having top salary with join

Perhaps:

SELECT TOP(1) name
FROM Tbl_employ e INNER JOIN Tbl_details d ON e.id = d.emp_id
ORDER BY d.salary DESC;

Essentially, this joins the two tables on the key fields (id and emp_id), returning only a single result (TOP(1)) that is the maximum salary row (ORDER BY d.salary DESC).

How to write SQL query to find max and min salary along with employee name in Oracle

Here is one way, using RANK. This answer makes one pass in a CTE to find the lowest and highest ranked salaries in the employee table. Then, it subqueries only once to retrieve the full matching records.

WITH cte AS (
    SELECT NAME, SALARY,
        RANK() OVER (ORDER BY SALARY) rnk_min,
        RANK() OVER (ORDER BY SALARY DESC) rnk_max
    FROM employee
)

SELECT NAME, SALARY
FROM cte
WHERE rnk_min = 1 OR rnk_max = 1
ORDER BY SALARY;

Demo

---

Related Topics