Sum of top n points from a table called points

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sum of top n points from a table called points

Author

Topic

jed100n
Starting Member

3 Posts

Posted - 2004-07-19 : 11:45:28

Hi there,

I'm having real trouble with this one.

I run a golf site whereby the league table is ordered in descending points order from a sum of the top 16 weeks worth of points from each member. There are more than 16 weeks in a season but the sum and therefore the order is only displayed on a particular members top 16 scores throughout the season.

I have a query which works for finding the top 16 points for an individual member as follows:

SELECT SUM(Points) AS t16, memname
FROM (SELECT TOP 16 Points, memname
FROM points
WHERE memname = 'dashin'
ORDER BY points DESC) DERIVEDTBL
GROUP BY memname
ORDER BY SUM(Points) DESC

But when I take the where memname = 'membername' out in order to display a full league table (of the sum of all members top 16 points), it returns an obscure set of results which are no good to me.

Any help would be really appreciated.

Thanks,

Mike.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-19 : 12:44:51

Create Table #points (memname nvarchar(25), points int)
Insert Into #points
Select 'Corey', 75 Union All
Select 'Corey', 79 Union All
Select 'Corey', 80 Union All
Select 'Corey', 77 Union All
Select 'Corey', 90 Union All
Select 'Corey', 115 Union All
Select 'Corey', 89 Union All
Select 'Jacob', 78 Union All
Select 'Jacob', 74 Union All
Select 'Jacob', 83 Union All
Select 'Jacob', 87 Union All
Select 'Jacob', 108 Union All
Select 'Jacob', 104 Union All
Select 'Jacob', 92

--top 5 becuase I'm lazy
Select top 5 memname, points from #points Where memname = 'Corey'

--top 5 again
Select
*
From
(
Select
memname,
points, rank = (Select count(Distinct points) From #points Where memname = A.memname and points <= A.points)
from #points as A
-- Where memname = 'Corey'
) as Z
Where rank <= 5
Order By memname, Rank

Insert Into #points Select memname = 'Corey', points = 79

--and again
Select
*
From
(
Select
memname,
points, rank = (Select count(Distinct points) From #points Where memname = A.memname and points <= A.points)
from #points as A
-- Where memname = 'Corey'
) as Z
Where rank <= 5
Order By memname, Rank

Drop Table #points

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-19 : 13:24:02

I woulnd't use Distinct in the counts; though -- other wise if you have:

10
10
20
30

the rank of 30 would be 3rd -- when it really should be 4th.

- Jeff

jed100n
Starting Member

3 Posts

Posted - 2004-07-19 : 13:28:43

Thanks Corey,

I'm a total SQL novice and I hope i've not misunderstood your kind response but my problem is this...

Here is a simplified table of where I get my data from initially:

Name.Points.Week
-------------------
Roger...12.....1
Tom.....11.....1
Mike....10.....1
Alan.....9.....1
Roger...12.....2
Alan....11.....2
Mike....10.....2
Tom......9.....2
Alan....12.....3
Mike....11.....3
Roger...10.....3
Tom......9.....3

So assuming I was working to the best top 2 results per member, my league table would look like this:

1. Roger (12+12) = 24
2. Alan (12+11) = 23
3. Mike (11+10) = 21
4. Tom (11+9) = 20

I just need to be able to return the memname and points in their respective descending orders.

Regards,

Mike.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-19 : 13:52:27

Create Table #points (memname nvarchar(25), points int)
Insert Into #points
Select 'Corey', 75 Union All
Select 'Corey', 77 Union All
Select 'Corey', 80 Union All
Select 'Corey', 79 Union All
Select 'Corey', 90 Union All
Select 'Corey', 115 Union All
Select 'Corey', 89 Union All
Select 'Jacob', 78 Union All
Select 'Jacob', 77 Union All
Select 'Jacob', 83 Union All
Select 'Jacob', 87 Union All
Select 'Jacob', 108 Union All
Select 'Jacob', 104 Union All
Select 'Jacob', 92

--top 2
Select
*
From
(
Select
memname,
points, rank = (Select count(points) From #points Where memname = A.memname and points >= A.points)
from #points as A
) as Z
Where rank <= 2
Order By memname, Rank

Select
memname,
points = sum(points)
From
(
Select
memname,
points, rank = (Select count(points) From #points Where memname = A.memname and points >= A.points)
from #points as A
) as Z
Where rank <= 2
Group By memname
Order By points Desc

Drop Table #points

Corey

jed100n
Starting Member

3 Posts

Posted - 2004-07-20 : 05:52:21

Pure genius, thank you Seventhnight - saved the day!

Mike.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources