SQL Syntax for Max Salary Record?

Wendy G

We would like the most recent salary amount. We used the query below but when we run the SQL we get the error that ORA-00923: FROM keyword not found where expected. Is there something wrong with the SQL for getting the most recent salary? Is there a better SQL to obtain that value?

Select Person_Number, Salary_Amount, StartDateAssignment, Date_From

From

(

SELECT P.Person_Number,

      A.Effective_Start_Date AS StartDateAssignment,

      SAL.Salary_Amount,

      A.Assignment_ID,

      AR.ACTION_REASON AS Reason,

SAL.Date_From

, ROW_NUMBER() OVER( Partition By P.Person_Number Order By SAL.Date_From desc, A.Effective_Start_Date desc) As RowNum

FROM Per_All_Assignments_f A

 INNER JOIN CMP_SALARY SAL ON SAL.Assignment_ID = A.Assignment_ID

 INNER JOIN Per_People_f P ON P.Person_ID = A.Person_ID

 INNER JOIN Per_Action_Reasons_TL AR ON AR.ACTION_REASON_ID = SAL.ACTION_REASON_ID

where p.person_number= '4002996'

) Dtable

Where RowNum = 1

Nathan CCC

ROWNUM is a reserved name. You cannot alias a column ROWNUM. Change it from "as RowNum" to as "r" or something then change the last line to where dtable.r=1.

Wendy G

Thank you. That got it.