Get top 3 in sql

问题: userid bank ------ ---- 1 2009 3 2009 3 2009 3 2009 3 2009 3 2009 2 2009 2 2009 1 2009 1 2009 6 2009 6 2009...

问题:

userid bank 
------ ----
1      2009  
3      2009
3      2009
3      2009
3      2009
3      2009
2      2009  
2      2009
1      2009
1      2009
6      2009
6      2009
6      2009
6      2009

Here is my question i want to get the top 3 of userid I want the sql gives me this:

userid 3: 5 times
userid 6: 4 times
userid 1: 3 times

How i can get this?


回答1:

In Standard SQL you can do aggregation with fetch first ... method :

select userid, count(*)
from table t
group by userid
order by count(*) desc
fetch first 3 rows only;

回答2:

In standard SQL you can use fetch first for this:

select userid, count(*)
from the_table
group by userid
order by count(*) desc
fetch first 3 rows only;

Online example: http://rextester.com/KLQFS86051


回答3:

And this would be the solution in SQL Server:

SELECT  TOP(3) id,
        COUNT(*) Cnt
FROM    t
GROUP BY id
ORDER BY COUNT(*) DESC

http://rextester.com/CUNDOJ55711


回答4:

The following approach is using window function and limit

select distinct userid, count(*) over(partition by userid) count_occ
from table t
order by count_occ desc
limit 3
  • 发表于 2018-07-13 23:38
  • 阅读 ( 249 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除