Find Top Items By Total Score

[bab5470]

I have a SQL table called KbRank with the following columns:

KBID RANKINGSCORE
1 100
2 50
1 100
1 75
3 0
5 25
2 100
3 100
1 0

I am attempting to write a SQL query that will find the top 3 KBID's with the highest cummulative ranking score. So I would want to add all of the RANKINGSCORE's for KBID1 up, all of the RANKINGSCORE's for KBID2 up etc, and then sort the results and grab the top 3.

I'm not much of a SQL guru but I think I would need to use a select statement with SUM to accomplish this. Can anyone point me in the right direction or offer any pointers?

Thanks
Brad

[jkmyoung]

http://www.w3schools.com/sql/sql_groupby.asp

The first example shows you how to get the sum of a group.
From there, sort on the sum,
and then select the first few rows using TOP eg: http://vorg.ca/626-the-MS-SQL-equiva...-limit-command

[pantan]

You'll need to use the SUM function and group by to calculate the total for each kbid, and use order by to list the kbid's in descending order. Finally, you'll leverage the subqueryconstruct to find the top 3 kbid's.

The SQL to use (this works in MySQL) is:

select kbid from
(select kbid, sum(rankingscore)
from kbrank
group by kbid
order by sum(rankingscore) desc) li1
limit 3;