[SQL] Convert row_number to SQL join and top1 functions

I am looking for this query to be re-written using sub-queries, top 1 or max function, however due to the nature of data in underlying tables, not able to achieve it. Appreciate if you can help on this.

SQL:

select * from
(
SELECT
ee.Employee_ID,
ee.Employer_ID,
eb.Amount
ROW_NUMBER() OVER (PARTITION BY ee.Employer_ID ORDER BY eb.End_Date DESC, ee.Employee_ID DESC) AS ROW_ID -- roll up to employer level
FROM
Benefit eb
INNER JOIN Employee ee
ON eb.Employee_ID= ee.Employee_ID
) a where ROW_ID=1


Sample Data:

Employee Table

Employee_ID Employer_ID
210100 AC
208584 AC
207599 DC


Employee Benefit Table

Employee_ID End_Date Amount
210100 25/02/2027 400
208584 25/01/2029 400
207599 25/02/2027 200


Expected Result

Employer_ID Employee_ID Amount
AC 208584 400
DC 207599 200


Tried below but not getting correct results:

SELECT
EE.Employee_ID,
EE.Employer_ID,
eb.Total_Amount
FROM
Employee ee
INNER JOIN Benefit EB
ON EE.Employee_ID = EB.Employee_ID
WHERE EE.Employee_ID = (
SELECT
MAX(Employee_ID) AS EMP_ID
FROM Employee ee2
WHERE EE.Employer_ID = EE2.Employer_ID
GROUP BY EE2.Employer_ID
)
AND EB.End_Date = (
SELECT
MAX(eb2.End_Date) AS END_DATE
FROM Benefit eb2
WHERE EB.Employee_ID = EB2.Employee_ID
GROUP BY EB2.Employee_ID
)
AND EE.Employer_ID = 'AC'

Continue reading...