Group and Max combination

• pdanes

  SSCrazy Eights

  Points: 8502

  More actions

  September 3, 2020 at 9:21 am

  #3785933

  ChrisM@Work wrote:

  Here's exactly what I meant by using Paul White's method to grab the first column values only, then using that result set as the source for further processing. The first operator in the plan is an index scan, but it only reads a single row from the index - the first. After that it's all index seeks, and it completes in 1ms using my 234,256-row sample data set:

  ;WITH FirstColumn AS (
  SELECT EvidenceLetter = MIN(e.EvidenceLetter)
  FROM #Evid e
  UNION ALL
  SELECT f.EvidenceLetter
  FROM (
  SELECT  e.EvidenceLetter,
  rn = ROW_NUMBER() OVER (ORDER BY e.EvidenceLetter)
  FROM #Evid e
  INNER JOIN FirstColumn f
  ON f.EvidenceLetter < e.EvidenceLetter
  ) f
  WHERE f.rn = 1
  )
  SELECT f.EvidenceLetter, c.EvidenceNumber, c.EvidenceExtra
  FROM FirstColumn f
  CROSS APPLY (
  SELECT TOP(1) EvidenceNumber, EvidenceExtra 
  FROM #Evid e 
  WHERE e.EvidenceLetter = f.EvidenceLetter
  ORDER BY EvidenceNumber DESC, EvidenceExtra DESC
  ) c
  OPTION (MAXRECURSION 0);

  I stand in awe. The first try still did one clustered index scan, but when I sprinkled in [EvidenceLetter <> '1e'] into the appropriate places, all is great. One index scan, two index seeks, all using the proper index, no sorts, the appropriate number of records (42) being moved around, and execution instantaneous.

   

  You were right, though - this is certainly not simple and obvious, or at least not to me.

   

  Many, many thanks - this is going to get some serious study. If you ever make it to Prague, the beer is on me.