问个db2 groupby后取count(*)最多的前10条遇到的问题
接触db2没多久，要做一个group by 后取count(*)前10条的功能 ，查了一下db2语法，翻页是靠rownumber() over() as rowno，在rowno between xx and xx做得，于是写了下面一条sql测试
select * from (select search_name as name,count(*) as count, rownumber() over() as rowno from yoyokm_activity_search where search_name !='' group by search_name order by count(*) desc ) as top
结果：
NAME COUNT ROWNO
--------------------------------------------------------------------------------------------------------------- ----------- --------------------
世纪之村 45 1
公司 42 2
test 30 3
install 28 4
1 17 5
名字2 13 6
吃 9 7
名字3 8 8
。。。。
top.rowno按照count(*)排序，看起来一切很正常，在加上between,
select * from (select search_name as name,count(*) as count, rownumber() over() as rowno from yoyokm_activity_search where search_name !='' group by search_name order by count(*) desc ) as top where top.rowno between 1 and 25
结果
NAME COUNT ROWNO
--------------------------------------------------------------------------------------------------------------- ----------- --------------------
世纪之村 45 12
公司 42 13
test 30 9
install 28 7
1 17 2
。。。。
count(*)最大的 top.rowno并不是最小！如果只取10条，
select * from (select search_name as name,count(*) as count, rownumber() over() as rowno from yoyokm_activity_search where search_name !='' group by search_name order by count(*) desc ) as top where top.rowno between 1 and 10
那就是一个错误的结果：
NAME COUNT ROWNO
--------------------------------------------------------------------------------------------------------------- ----------- --------------------
test 30 9
install 28 7
1 17 2
。。。。。
请问这是为什么？我要group by后根据count(*)取前10条，应该怎么做？
[解决办法]
你里面排了序，但外面这层select没有排序，所以就乱了
你把order by count放到外面来
[解决办法]
select * from (select top.*, rownumber() over() as rowno from (select search_name as name,count(*) as count from yoyokm_activity_search where search_name !='' group by search_name order by count(*) desc ) as top) top where top.rowno between 1 and 10
换成这样试试
------解决方案--------------------
如果按照总数排序结果 的话 你考虑下rank 函数
