Query Performance

• briconaway

  SSC Rookie

  Points: 35

  More actions

  May 24, 2010 at 9:44 pm

  #387002

  I have a table below

  VK PK FK Amt

  3 130 129 100

  3 130 130 100

  3 130 131 100

  4 130 129 10

  4 130 130 10

  The Results I want....

  VK PK FK Amt

  4 130 129 10

  4 130 130 10

  3 130 131 100

  Essentially, if the PK and VK match for a respective VK, I want to use the values from the VK = 4 and not 3.

  Two thoughts come to mind, I could insert all the 3's into a temp table and then update the temp tables on the 4's and return the temp table

  or...

  I could do something like (if this query below would even work).

  Select *

  From table Z

  LEFT JOIN

  (Select MAX(vk), PK, FK from table group by PK, FK) X

  ON Z.VK = X.VK AND X.PK = Z.PK AND X.FK = Z.FK

  Over the course of time, the data set is going to get large.

  What is the best solution for this problem in terms of performance?

  Thanks in Advance

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 25, 2010 at 1:59 am

  #1171901

  This should do the trick for you:

  DECLARE @test-2 TABLE (

  VK int,

  PK int,

  FK int,

  Amt int

  )

  INSERT INTO @test-2

  SELECT 3, 130, 129, 100 UNION ALL

  SELECT 3, 130, 130, 100 UNION ALL

  SELECT 3, 130, 131, 100 UNION ALL

  SELECT 4, 130, 129, 10 UNION ALL

  SELECT 4, 130, 130, 10

  ;WITH RankedTest AS (

  SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY PK, FK ORDER BY VK DESC)

  FROM @test-2 AS A

  )

  SELECT *

  FROM RankedTest

  WHERE RN = 1

  Hope this helps

  Gianluca

  -- Gianluca Sartori