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.
它返回所有1的排名。
3 个解决方案
#1
4
If pricingHistoryId is the Primary Key, Partitioning by it always returns the rank as 1 because there cannot be repetitive primary keys!
如果pricingHistoryId是主键，则它的分区总是将等级返回为1，因为不能有重复的主键！
#2
1
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.
行号应用于每个分区并重置为下一个分区。您需要“分区”您想要编号的组。如果您想在整个结果集中使用一个序列，请完全删除“PARTITION BY ph.pricingHistoryId”并保留“ORDER BY”部分。
#3
0
Agree with @Akhil. It means ph.pricingHistoryId is unique.
同意@Akhil。这意味着ph.pricingHistoryId是独一无二的。
#1
4
If pricingHistoryId is the Primary Key, Partitioning by it always returns the rank as 1 because there cannot be repetitive primary keys!
如果pricingHistoryId是主键，则它的分区总是将等级返回为1，因为不能有重复的主键！
#2
1
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.
行号应用于每个分区并重置为下一个分区。您需要“分区”您想要编号的组。如果您想在整个结果集中使用一个序列，请完全删除“PARTITION BY ph.pricingHistoryId”并保留“ORDER BY”部分。
#3
0
Agree with @Akhil. It means ph.pricingHistoryId is unique.
同意@Akhil。这意味着ph.pricingHistoryId是独一无二的。