雪花云数据平台DENSE_RANK()OVER(PARTITION by

Question

我已经：DENSE_RANK((OVER(按州划分，按人口递减(作为排名

我想跳过排名的前10个结果，并将其限制在每个州50个结果。这可能吗？

Answer 1

so：

with data(state, pop) as (
select * from values 
(1,10),
(1,10),
(1,11),
(1,12),
(2,10),
(2,11),
(2,12),
(2,12)
)
select d.*
,DENSE_RANK() OVER ( PARTITION BY state ORDER BY pop desc) as ranking
from data as d

给出：

排名<1>2

STATE	POP
2	1	1
2	1	1
2	11	2
2	10	3
1	12
1	11
1	10	3
1	10	3