Help with query! Ranking of Sum() column

If anyone can help with this, I'd be most appreciative.
Basically, I'm trying to sum a column based on a unique ID and then
find out the RANK of that record in the table.
Here's the table:
Points
- PointID
- UserID
- Points
There can be multiple records with the same UserID.
Here's the query I'm using now:
SELECT Sum(Points), UserID FROM Points Group By UserID Order By
Sum(Points) desc
This basically returns ALL the records with the Points summed. I think
loop through in my code to find what row number a specific ID is. This
is NOT efficient and is slowing down my site considerably.
This is a query someone recommended I used:
SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
sum(points) FROM Points WHERE UserId = 65))
SQL Server doesn't like that query, though, because of the aggregate
function. I was searching for the rank of UserID 65.
If anyone could help me with this I'd appreciate it. I ONLY need the
rank of one record, so hoepfully I don't need to use a temp table for
this.
Thanks,
Andyhttp://www.aspfaq.com/show.asp?id=2427
<andymilk@.gmail.com> wrote in message
news:1146492469.413328.220320@.j33g2000cwa.googlegroups.com...
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>|||Try,
-- sql server 2000
create view v1
as
select UserID, sum(Points) as sum_Points
from t1
group by UserID
go
select * from v1
go
select
count(*) as rank,
a.UserID,
a.sum_Points
from
v1 as a inner join v1 as b
on (a.sum_Points < b.sum_Points)
or (a.sum_Points = b.sum_Points and a.UserID >= b.UserID)
group by
a.UserID,
a.sum_Points
order by
rank
go
AMB
"andymilk@.gmail.com" wrote:

> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>