CODEWITHSUNDEEP
sql
user4876846
user4876846

Reputation: 13

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
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?

Upvotes: 1

Views: 5033

Answers (4)

zipa
zipa

Reputation: 27869

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

Upvotes: 1

tourist
tourist

Reputation: 4333

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 2

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions