Thursday, January 7, 2016

Select the top 1 row from each group - MSSQL 2012



Two answers from Stack overflow. Opinion divided about which answer gives better performance.
We have used second answer with left join


http://stackoverflow.com/a/15380875/3587767

Using CTE

WITH cte 
     AS (SELECT id, 
                userid, 
                version, 
                datetime, 
                Row_number() 
                  OVER ( 
                    partition BY userid 
                    ORDER BY Cast(version AS INT) DESC) rn 
         FROM   [dbo].[table]) 
SELECT id, 
       userid, 
       version, 
       datetime 
FROM   cte 
WHERE  rn = 1 
ORDER BY userid
http://stackoverflow.com/a/15380976/3587767
SELECT a.* FROM MyTable a
LEFT JOIN MyTable b
  ON a.userid=b.userid
 AND CAST(a.version AS INT) < CAST(b.version AS INT)
WHERE b.version IS NULL