CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Sam
Sam

Reputation: 4339

ROW_NUMBER and PARTITION query returning all rows ranked as 1

I am having issues with a query, I want it to rank the result based on the time the last change was recorded.

SELECT
  ROW_NUMBER() OVER (PARTITION BY ph.pricingHistoryId ORDER BY ph.changeRecorded DESC),
  ph.*
FROM
  PriceHistory ph

It returns all 1 for the ranking.

Upvotes: 0

Views: 3092

Answers (2)

Akhil
Akhil

Reputation: 7610

If pricingHistoryId is the Primary Key, Partitioning by it always returns the rank as 1 because there cannot be repetitive primary keys!

Upvotes: 4

Glenn
Glenn

Reputation: 9170

The row number is applied to each partition and reset for the next partition. You need to "partition over" the group you want numbered. If you want one sequence over the entire result set, remove the "PARTITION BY ph.pricingHistoryId" entirely and just keep the "ORDER BY" part.

Upvotes: 1

Related Questions