5

This is type of query I'm trying to run:

WITH CTE_Ordered AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId
  FROM SourceTable
),
CTE_Top1_PerPartition AS
(
  SELECT *
  FROM CTE_Ordered
  WHERE PartitionRowId = 1
),
CTE_Calculations AS
(
  SELECT AVG(NumberField1) AS NumberField1_Avg, StdDev.StdDev AS NumberField1_StdDev
  FROM CTE_Top1_PerPartition 
  CROSS JOIN
  (
     SELECT STDEV(NumberField1) AS StdDev
     FROM CTE_Top1_PerPartition 
  ) AS StdDev
  GROUP BY StdDev.StdDev
)

-- Final Select
SELECT *
FROM CTE_Calculations

Every time I run the final select, my NumberField1_StdDev value changes even though SourceTable is isolated and doesn't change.

I noticed if I select CTE_Top1_PerPartition into a temp table first and then run the rest of the query off of that temp table, then I get the same result for NumberField1_StdDev every time.

I would guess it has something to do with the order the results are returned in the CTE_Top1_PerPartition CTE, but I'm not sure why.

1
  • 4
    If your source table has unique key, replace (PARTITION BY PartitionField ORDER BY DateField) by (PARTITION BY PartitionField ORDER BY DateField, source_table_unique_key) Jan 30, 2020 at 23:42

1 Answer 1

16

row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values). Any of the tied values might end up with a PartitionRowId of 1 which would presumably change the final result.

You could use rank instead of row_number but that would cause you to consider all the tied rows which may not be what you want. You could also update your analytic function to add additional order by criteria to ensure that row_number returns a deterministic first row.

1
  • Ah good point, this is a large dataset where there very well could be multiple records in my partition that have the same exact DateField (technically DateTime). Yours and Piotr's suggestion to add additional criteria such as the key to the ORDER BY clause of my ROW_NUMBER() window function would be the correct fix.
    – J.D.
    Jan 31, 2020 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.