开发者

Ranking functions from a given number in SQL Server 2008

select 
    row_number() over (order by (select 1)) as Rank,
SalesAmount  
from
    dbo.FactInternetSales 

This will give you:

          Rank,      SalesAmount  
          1,         3578.27
          2,         3399.99 
          3,         3399.99
          4,         699.0982
          5,         799.0982

but I want to get like this: skip up to the number I pass @skipnum (e.g. 10)

          Rank,开发者_开发百科       SalesAmount  
          11,         3578.27
          12,         3399.99 
          13,         3399.99
          14,         699.0982
          15,         799.0982

Is there anyway in SQL Server 2008 to get like this using ranking functions not cursors?


SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) + @skipnum AS [Rank], ...

However why are you using SELECT 1 to indicate rank? Shouldn't rank actually be meaningful, e.g. ORDER BY SalesAmount DESC? Also if you expect them to come back 11, 12, 13... you should add an outer ORDER BY...


Just add @skipnum to the function

select 
    row_number() over (order by (select 1)) + @skipnum as Rank,
    SalesAmount  
from
    dbo.FactInternetSales 

The OVER (ORDER BY (SELECT 1)) means a arbitrary order so I hope it's just for the question...


select
    @skipnum + row_number() over (order by (select 1)) as Rank,
    SalesAmount  
from
    dbo.FactInternetSales
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜