CODEWITHSUNDEEP
sqlsql-servermaxrow-number
user20119813
user20119813

Reputation: 3

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

EmployeeBenefit 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

I tried with this SQL, but I'm not getting the 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'

Upvotes: 0

Views: 112

Answers (1)

antonio_mg
antonio_mg

Reputation: 492

What about CROSS APPLY? If you have an Employer table this should work:

SELECT e.[Employer_ID], eb.[EndDate], eb.[Amount]
FROM [Employer] e
CROSS APPLY (
    SELECT TOP 1 b.[EndDate], b.[Amount]
    FROM [Employee] ee
    INNER JOIN [EmployeeBenefit] b ON b.[Employee_ID] = ee.[EmployeeID]
    WHERE ee.[Employer_ID] = e.[Employer_ID]
    ORDER BY b.[EndDate] DESC, e.[Employee_ID] DESC
) eb

If you don't have an Employer table, you need first get DISTINCT employers in a subquery or Common Table Expression for "PARTITION BY" and then CROSS APPLY

Upvotes: 1

Related Questions