# Find the Latest Salary of each Employee in Employer table

How to find the latest salary of each employee in the Employer table?
emp.id sal year month
1001 5000 2015 3
1001 3000 2014 4
1002 4000 2013 3
1003 2000 2013 2

#### Anish

• Jan 16th, 2016

```CodeSELECT EMP_ID,SAL
FROM EMPLOYEE
WHERE (YEAR,MONTH) IN (SELECT MAX(YEAR),MAX(MONTH) FROM EMPLOYEE
GROUP BY EMP_ID )
GROUP BY EMP_ID;```

#### Anish

• Jan 19th, 2016

The below query will hold for all the possible scenarios.

```CodeSELECT EMP_ID,SAL
FROM EMPLOYEE
WHERE YEAR IN (SELECT MAX(YEAR) FROM EMPLOYEE
GROUP BY EMP_ID )
AND MONTH IN (SELECT MAX(MONTH) FROM EMPLOYEE
GROUP BY EMP_ID,YEAR )
GROUP BY EMP_ID;```

#### Mark Law

• Jan 23rd, 2016

Assuming the salary of an employee will not change in the same month,

```CodeSELECT E.ID, E.SALARY AS LATEST_SALARY FROM
EMPLOYEE E
JOIN
(
SELECT T1.ID, T1.MAX_YEAR, MAX(T2.MONTH) AS MAX_MONTH
FROM
(SELECT ID, MAX(YEAR) AS MAX_YEAR FROM EMPLOYEE GROUP BY ID) T1
JOIN
EMPLOYEE T2
ON
(T1.ID = T2.ID AND T1.MAX_YEAR = T2.YEAR)
GROUP BY T1.ID, T1.MAX_YEAR
) R
ON (E.ID = R.ID AND E.YEAR = R.MAX_YEAR AND E.MONTH = R.MAX_MONTH)
```

#### anagogia

• Sep 29th, 2016

The query has to be a nested query because one employee could have the max (year||month) = 201503 where he had max salary and other could have max(year||month) = 201603 where he had the max salary .... Inner query needs to have a joining condition with the outer query !

DATA
1 100000 2015 3
1 80000 2014 5
2 100000 2015 7
2 90000 2014 6
3 90000 2015 9
3 110000 2014 9

```CodeSELECT E.EMP_ID, E.SALARY, E.YEAR||LPAD(E.MONTH, 2, 0) MAX_SALARY_YEAR_MONTH
FROM TEMP_EMPLOYEE E
WHERE TO_DATE(LPAD(E.MONTH, 2, 0)||E.YEAR, MMYYYY) =
( SELECT MAX(TO_DATE(LPAD(F.MONTH, 2, 0)||F.YEAR, MMYYYY))
FROM TEMP_EMPLOYEE F
WHERE F.EMP_ID = E.EMP_ID )```

#### SANTHOSH KUMAR GUJJA

• Nov 25th, 2017

SEL EMP.ID,SAL FROM
EMPLOYER QUALIFY ROW_NUMBER() OVER(PARTITION BY EMP.ID ORDER BY YEAR,MONTH DESC)=1