A question for the experts regarding Rank sql2000

I am trying to rank a table but cannot get the desired
result.
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
3 Sally 26
4 Tim 23
I am getting the above results from this query
SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points >= U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
I would like to achieve the following result. Is this possible?
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23
Your help would be apreciated.Please provide DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Ross" <andrew.ross@.racingworld.com> wrote in message
news:1124229285.283143.263590@.g43g2000cwa.googlegroups.com...
> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Do:
SELECT t1.alias,
SUM( CASE WHEN t2.points >= t1.points
THEN 1 ELSE 0 END) -
SUM( CASE WHEN t2.points = t1.points
THEN 1 ELSE 0 END) + 1 AS "rank"
FROM Users t1, Users t2
GROUP BY t1.alias
ORDER BY "rank" ;
The idea is to identify the number of ties within the dataset. For an easy
alternative, consider using the following query with subqueries:
SELECT t1.*,
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points >= t1.points) AS "rank",
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points = t1.points ) AS "tied counts"
FROM Users t1 ;
You can manipulate this query making it a derived table construct.
Anith|||Try
SELECT COUNT(U2.Points) + 1 AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points > U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
Regards,
Willson
http://www.wsantoso.net
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Hi Willson
This works great except that it drops the first record (1 Bud
82)
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23|||Oh yeah that, change the inner join to left outer JOIN as it's comparing
against those that has bigger point to get the rank. Seriously this is
something that is a lot more efficient to run on the client side rather than
SQL as noted by many other people in your previous thread.
"Ross" wrote:

> Hi Willson
> This works great except that it drops the first record (1 Bud
> 82)
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
>|||Thanks that works fine|||create table t (Name varchar(10), Points int)
go
insert into t
select 'Bud', 82 union all
select 'Bill', 37 union all
select 'Fred', 37 union all
select 'Sally', 26 union all
select 'Tim', 23
go
select a.Name,
a.Points,
(select count(distinct x.points) from t x where x.points >= a.points) [Rank]
from t a
order by rank
Rakesh
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>