Hi
I have the Folowing table
NAME DATE JOB_ID SALARY TYPE
John 2005.09.28 FI_ACCOUNT 8200 X
John 2002.08.16 FI_ACCOUNT 9000 Y
John 2007.12.07 FI_ACCOUNT 6900 E
John 2005.09.30 FI_ACCOUNT 7700 E
John 2006.03.07 FI_ACCOUNT 7800 X
Bruce 2007.05.21 IT_PROG 6000 X
Bruce 2007.02.07 IT_PROG 4200 Y
Bruce 2006.02.05 IT_PROG 4800 E
Bruce 2005.06.25 IT_PROG 4800 X
Shelli 2005.12.24 PU_CLERK 2900 X
Shelli 2005.07.24 PU_CLERK 2800 X
Shelli 2006.11.15 PU_CLERK 2600 X
Shelli 2007.08.10 PU_CLERK 2500 X
I need to create a SQL Query that return a culumns NAME , DATE , JOB_ID SALARY, TYPE for the last DATE and 2 new columuns that return the max Salary when the type E or Y.
Exemple:
NAME DATE JOB_ID SALARY TYPE MAX(SALARY_E) MAX(SALARY_Y)
John 2007.12.07 FI_ACCOUNT 6900 E 7700 9000
Bruce 2007.05.21 IT_PROG 6000 X 4800 4200
Shelli 2007.08.10 PU_CLERK 2500 X NULL NULL
could you help me to find the best way to do it? i was thinking using a analitic funtion but i dont know if it´s possible