How Select Max(Salary) of Employee Each Department With Employee_Id and Emp_Name

how select max(salary) of employee each department with employee_id and emp_name

Use a subquery with department_id and the max salary and then join to the main table:

select 
  e.department_id, 
  t.employee_id as id,
  t.first_name || ' ' || t.last_name as name,
  e.maxsalary 
from (
  select 
    department_id, 
    max(salary) as maxsalary 
  from 
    EMPLOYEES
  group by 
    department_id 
) e
inner join 
  EMPLOYEES t 
on 
  t.department_id = e.department_id and t.salary = e.maxsalary
order by e.department_id;

See the demo

EMPLOYEES

EMPLOYEE_ID DEPARTMENT_ID   SALARY  FIRST_NAME  LAST_NAME
    1          1            10000       A           B
    2          1            20000       C           D
    3          1            150000      E           F
    4          2            12000       G           H
    5          2            10000       I           J
    6          3            20000       K           L
    7          4            11000       M           N
    8          4            11000       O           P
    9          4            11000       Q           R
    10         4            10000       S           T

Result

DEPARTMENT_ID   ID  NAME    MAXSALARY
    1            3  E F      150000
    2            4  G H       12000
    3            6  K L       20000
    4            7  M N       11000
    4            8  O P       11000
    4            9  Q R       11000

Highest Salary in each department

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

The above query is the accepted answer but it will not work for the following scenario. Let's say we have to find the employees with the highest salary in each department for the below table.

WITH RCounter AS (
    SELECT department_id, last_Name, Salary,
           DENSE_RANK() OVER(PARTITION BY department_id ORDER BY Salary DESC) AS RowNumberCounter
        FROM <<tablename>>
)
SELECT department_id, last_Name, Salary
    FROM RCounter 
    WHERE RowNumberCounter = 1;