Search This Blog

Thursday, February 9, 2012

How to take 1 record from an INNER JOIN

Hit a problem moving data between systems where there is legitimate duplicate mappings between the two systems but I need just one.  and It really does not matter which one.  basically I want to take SELECT TOP 1 on records coming off a join:
I should use this approach more but it can be slower under some conditions

SELECT
        A.RetailerID,
        X.RetailerIDTheirs
FROM TableA AS A
CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE B.RetailerID = A.RetailerID 
ORDER BY B.RetailerIDTheirs ASC) AS X






No comments: