ROW_NUMBER(): An Efficient Alternative to Subqueries

• Brian Barkauskas

  Old Hand

  Points: 306

  More actions

  June 18, 2010 at 3:26 pm

  #1182365

  -- how about?

  SELECT

  pv.ProductID

  , pv.Version

  , pv.MinorVersion

  , pv.ReleaseVersion

  , pv.StandardCost

  FROM Production.ProductVersion pv WITH (NOLOCK)

  WHERE EXISTS (

  SELECT *

  FROM (

  SELECT TOP 1

  pv2.ProductID

  , pv2.Version

  , pv2.MinorVersion

  , pv2.ReleaseVersion

  FROM Production.ProductVersion pv2 WITH (NOLOCK)

  WHERE pv2.ProductID = pv.ProductID

  ORDER BY

  pv2.ProductID DESC

  , pv2.Version DESC

  , pv2.MinorVersion DESC

  , pv2.ReleaseVersion DESC

  ) pv2

  WHERE pv2.ProductID = pv.ProductID

  AND pv2.Version = pv.Version

  AND pv2.MinorVersion = pv.MinorVersion

  AND pv2.ReleaseVersion = pv.ReleaseVersion

  )