CODEWITHSUNDEEP
sqlsql-server
Daniel Vivas
Daniel Vivas

Reputation: 19

What is the alternative of First for SQL Server?

I am reverse engineering a MS Access query that flags duplicates and then decides which one to keep based on some conditions. I found the duplicates using a group by statement. My issue is finding a way to write the conditions down. What the MS Access query did was sort by the conditions and then used the FIRST function to pick the row that fulfilled most of the conditions. SQL Server does not use FIRST, any ideas to go around FIRST to do this?

The best I have been able to think of was using max(Opportunity) which would return an instance of the field but without any of the criteria to pick the duplicate.

MS Access query:

SELECT Dup_A_Open_04_Temp1.[RequFX_Req_ID_Cont_ID:],
 First(Dup_A_Open_04_Temp1.[Oppty ID]) AS [FirstOfOppty ID], First(Dup_A_Open_04_Temp1.[Op id]) AS [FirstOfOp id], "Surving Oppty" AS Dup_A_Resolution
FROM Dup_A_Open_04_Temp1
GROUP BY Dup_A_Open_04_Temp1.[RequFX_Req_ID_Cont_ID:], "Surving Oppty";

[FirstOfOppty ID] and [FirstofOp id] are the ones that have to be picked by the conditions.

Upvotes: 1

Views: 549

Answers (2)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Using ROW_NUMBER() to identify duplicate rows:

SELECT t1.[RequFX_Req_ID_Cont_ID:], t1.[Oppty ID] AS [FirstOfOppty ID],
    t1.[Op id] AS [FirstOfOp id], 
    ROW_NUMBER() OVER(PARTITION BY t1.[RequFX_Req_ID_Cont_ID:] ORDER BY t1.[Oppty ID]) AS ROWNUMBER
FROM Dup_A_Open_04_Temp1 t1
WHERE ROWNUMBER > 1

Upvotes: 1

GMB
GMB

Reputation: 222502

You should be able to use window function RANK() ... OVER(). The partition corresponds to the non-aggregated columns of your original query:

SELECT *
FROM (
    SELECT 
        [RequFX_Req_ID_Cont_ID:],
        [Oppty ID] AS [FirstOfOppty ID], 
        [Op id] AS [FirstOfOp id],
        "Surving Oppty",
        RANK() OVER(PARTITION BY Dup_A_Open_04_Temp1.[RequFX_Req_ID_Cont_ID:] ORDER BY Opportunity DESC) rn
    FROM Dup_A_Open_04_Temp1 t
) x
WHERE rn = 1

Note: since your original query has no ordering, it is hard to tell exactly which column should be used in the ORDER BY clause of the window RANK(). Based on your description of the problem, I picked column Opportunity. You might need to adapt this to your exact use case.

Upvotes: 0

Related Questions