SQL Server RANK & DENSE_RANK as aggregate(not analytic) function

hwazgwia 于 12个月前发布在其他

关注(0)|答案(1)|浏览(122)

Is there any option to use RANK/DENSE_RANK function in SQL Server as aggregate? For example the below query in Oracle/Postgres returns hypothetical ranking of the value 200 in the orders table based on the order's gross value.

SELECT RANK(200) WITHIN GROUP (ORDER BY gross_value ASC)
   FROM orders;

RANK/DENSE_RANK with OVER clause works quite different(as analytic function) and that's not what I want to get.

sql-server

来源：https://stackoverflow.com/questions/77678494/rank-dense-rank-as-aggregatenot-analytic-function

1条答案

按热度按时间

db2dz4w81#

You can work out what the ranking would be by getting the highest rank for all values below that. If there is none then ISNULL it to 1 .

SELECT ISNULL(MAX(rn) + 1, 1)
FROM (
    SELECT rn = RANK() OVER (ORDER BY gross_value)
    FROM orders
    WHERE gross_value < 200
) t;

For descending order you do it like this

SELECT ISNULL(MAX(rn) + 1, 1)
FROM (
    SELECT rn = RANK() OVER (ORDER BY gross_value DESC)
    FROM orders
    WHERE gross_value > 200
) t;

赞(0）回复(0）举报 12个月前

我来回答

SQL Server RANK & DENSE_RANK as aggregate(not analytic) function

1条答案

相关问题

热门标签

最新问答