SQL Query to Find employee with MAX salary department wise

SELECT * FROM(

SELECT Employee_Id,

First_Name,

Department_Id,

Salary,

RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) as SAL_RANK

FROM EMPLOYEES

)

WHERE SAL_RANK =1;

Find the oldest joinee department wise using LEAD Analytic function

LEAD is exact opposite of the LAG Analytic function. LAG fetches the row details of previous record and LEAD fetches the row details of next record.

SELECT Employee_Id,

First_Name,

Department_Id,

Hire_date,

LEAD(Hire_date) OVER(PARTITION BY Department_Id ORDER BY Hire_date DESC) as PREV_HIREDATE

FROM EMPLOYEES

ORDER BY Department_Id, Hire_date;

Since LEAD fetches the next value we used the DESC in the ORDER BY clause so that the LEAD Analytic function starts working from latest Hire date and keep picking the hire date of the employee joined before him. So finally for the oldest joinee the value will be NULL.

The below query fetches the oldest joinee details department wise:

SELECT * FROM(

SELECT Employee_Id,

First_Name,

Department_Id,

Hire_date,

LEAD(Hire_date) OVER(PARTITION BY Department_Id ORDER BY Hire_date DESC) as PREV_HIREDATE

FROM EMPLOYEES)

WHERE PREV_HIREDATE IS NULL;

Also Read :

What is a Synchronization task in Informatica?

What is a Runtime environment in Informatica?