CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Remo
Remo

Reputation: 389

Problem with top 10 rows in a sql query?

As a beginner in Sql Server 2005, i should get some help in getting top 10 from a table.

The scenario is like, a table Invitecount has multiple records for each userid. i have distinct userid's with the following sql query

Select distinct(userid) from inviteCount

For each userid, i get the number of points using the following query

Select sum(points) from invitecount 
where UserID = 126429

And after i get the sum, i should have top 10 users with max points. My problem here is with writing all these statements together using arrays, etc.

IF someone could help me, i really appreciate it. Thanks in advance!

Upvotes: 2

Views: 339

Answers (3)

Eljakim
Eljakim

Reputation: 6937

Try this:

Select TOP 10 userid, sum(points) from inviteCount group by userid order by sum(points) desc

Upvotes: 3

Jon Egerton
Jon Egerton

Reputation: 41549

You want something like:

select top 10
    userid,
    pointstotal = sum(points)
from
    invitecount
group by userid
order by sum(points) desc

Note the order by.

A fancier version would be

select 
    userid
    pointstotal = sum(points) over (partition by userid),
    row_number = row_number() over (partition by userid order by sum(points) desc)
from 
    invitecount i
where 
    row_number <= 10

(untested - so probably needs a tweak)

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85056

Try this:

SELECT TOP 10 userID, SUM(Points)
FROM inviteCount
GROUP BY UserId
ORDER BY SUM(Points) desc

I'm not sure what you mean by using arrays, but this would get you the top ten userIds ordered by the sum of points.

Upvotes: 5

Related Questions