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

Showing Answers 1 - 5 of 5 Answers

Anish

  • Jan 16th, 2016
 

Code
  1. SELECT EMP_ID,SAL

  2. FROM EMPLOYEE

  3. WHERE (YEAR,MONTH) IN (SELECT MAX(YEAR),MAX(MONTH) FROM EMPLOYEE

  4.                       GROUP BY EMP_ID )

  5. GROUP BY EMP_ID;

  Was this answer useful?  Yes

Anish

  • Jan 19th, 2016
 

The below query will hold for all the possible scenarios.

Code
  1. SELECT EMP_ID,SAL

  2. FROM EMPLOYEE

  3. WHERE YEAR IN (SELECT MAX(YEAR) FROM EMPLOYEE

  4.                       GROUP BY EMP_ID )

  5. AND MONTH IN (SELECT MAX(MONTH) FROM EMPLOYEE

  6.                       GROUP BY EMP_ID,YEAR )

  7. GROUP BY EMP_ID;

  Was this answer useful?  Yes

Mark Law

  • Jan 23rd, 2016
 


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

Code
  1. SELECT E.ID, E.SALARY AS LATEST_SALARY FROM

  2. EMPLOYEE E

  3. JOIN

  4. (

  5.         SELECT T1.ID, T1.MAX_YEAR, MAX(T2.MONTH) AS MAX_MONTH

  6.         FROM

  7.                 (SELECT ID, MAX(YEAR) AS MAX_YEAR FROM EMPLOYEE GROUP BY ID) T1

  8.         JOIN

  9.                 EMPLOYEE T2

  10.         ON

  11.         (T1.ID = T2.ID AND T1.MAX_YEAR = T2.YEAR)

  12.         GROUP BY T1.ID, T1.MAX_YEAR

  13. ) R

  14. ON (E.ID = R.ID AND E.YEAR = R.MAX_YEAR AND E.MONTH = R.MAX_MONTH)

  15.  

  Was this answer useful?  Yes

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

Code
  1. SELECT E.EMP_ID, E.SALARY, E.YEAR||LPAD(E.MONTH, 2, 0) MAX_SALARY_YEAR_MONTH

  2. FROM TEMP_EMPLOYEE E

  3. WHERE TO_DATE(LPAD(E.MONTH, 2, 0)||E.YEAR, MMYYYY) =

  4.   ( SELECT MAX(TO_DATE(LPAD(F.MONTH, 2, 0)||F.YEAR, MMYYYY))

  5.     FROM TEMP_EMPLOYEE F

  6.     WHERE F.EMP_ID = E.EMP_ID )

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions