Convert row_number to SQL join and top1 functions

Question

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'

Windows functions like row_number() generally preform better with less code than the alternative of joining to a subquery. The only meaningful reason to rewrite this is to support an older database that doesn't do windowing functions yet, and in sql server land all such versions are fully end of life, meaning they no longer get any updates... not even critical security patches. It would be irresponsible to give you a solution in that circumstance. — Joel Coehoorn
– Joel Coehoorn, Commented Oct 14, 2024 at 0:48
We are using indexed views for such queries and have tested such queries converting to use SQL joins and TOP 1 function. Post converting to joins, view output is fast comparably, so wanted to give a test and compare for this query as well. Since this SQL have two order by from two different tables, not converting straight forward. So just looking for opinion if this can be converted or not to test. — user20119813
– user20119813, Commented Oct 14, 2024 at 3:07
The original query does not produce the desired result shown. The original query results in one row per employee. The desired result shown is one row per date, I suppose? So, what exactly are you looking for: A query without ROW_NUMBER that produces the desired result or a query without ROW_NUMBER that produces the same result as the original query? — Thorsten Kettner
– Thorsten Kettner, Commented Oct 14, 2024 at 6:50
I'd say you'd be wasting time, how many rows do you have in those tables? — siggemannen
– siggemannen, Commented Oct 14, 2024 at 6:52

antonio_mg · Accepted Answer · 2024-10-14 07:12:51Z

1

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

edited Oct 14, 2024 at 7:12

answered Oct 14, 2024 at 6:29

antonio_mg

4921 gold badge5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert row_number to SQL join and top1 functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related