3

Quick question based on some interesting syntax I've found out. I have a table with these columns:

ID INT,
DimBuyDetails_Id INT, 
WeekOfBuy INT,
Spots INT,
Retired BIT

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY ID --<
) AS ct
FROM Core.FactBuysPerWeek

However, if I change the ORDER BY clause I'll get the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY DimBuyDetails_Id, WeekOfBuy --<Changed Order by here
) AS ct
FROM Core.FactBuysPerWeek

Anyone know what causes this?

0
4

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

OVER Clause (Transact-SQL)

If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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