sql 2000 ranking - SQL Server Forums

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

Transact-SQL (2000)

sql 2000 ranking

Author

Topic

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 16:26:28

Hello,

I am having an issue with a SQL query I wrote in SQL 2000. I am almost there but am lacking in one area, hoping a better programmer than me and a fresh set of eyes can pick off my mistake.

The goal is very straight forward, order and rank the following fields:
R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference

The issue lies in my sub query in the AND / OR joins... This query will work if I sort and rank on two fields, but if I try three or more it does not work properly.

Here is my entire query:

SELECT

TOP 100 PERCENT

R1.AutoPoolID,
R1.PoolType,
R1.Week,
R1.Username,
R1.TieBreakerTotal,
R1.Wins,
R1.Losses,
R1.Ties,
R1.TBActual,
R1.TBDifference,
R1.WinPercentage,
R1.Points_Thursday,
R1.Points_Saturday,
R1.Points_Sunday,
R1.Points_Monday,
R1.Points_Total,

(
SELECT COUNT(*)
FROM tblWeeklyStandings_All R2
WHERE R2.AutoPoolID = R1.AutoPoolID AND R2.Week = R1.Week AND
(
R2.PSWins > R1.PSWins OR
R2.PSWins = R1.PSWins AND
R2.PSPoints_Total > R1.PSPoints_Total OR
R2.PSPoints_Total = R1.PSPoints_Total AND R2.PSTBDifference < R1.PSTBDifference
)
) + 1 AS Rank

FROM dbo.tblWeeklyStandings_All R1 INNER JOIN
dbo.qryUsers_SDR ON R1.AutoPoolID = dbo.qryUsers_SDR.AutoPoolID AND R1.PoolID = dbo.qryUsers_SDR.PoolID
WHERE
(R1.PoolType = '2007' OR R1.PoolType = '2008') AND
(dbo.qryUsers_SDR.OrderBy1 = '6') AND
(dbo.qryUsers_SDR.PointSpread = 1)

ORDER BY R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference

At this point I am ready to buy SQL 2005 because I think this gets easier, hoping a 2000 wiz can help me correct my error, or missing "(", or something!

Here are the results:
Rank Username Wins Points TB Difference
1. test 0618084 16 50 0
4. test 0618083 16 50 66
3. test 0618081 15 50 55
4. test 0618082 14 50 55
5. admin 2 0 0

As you can see the ranking is not correct. It should be the following:
Rank Username Wins Points TB Difference
1. test 0618084 16 50 0
2. test 0618083 16 50 66
3. test 0618081 15 50 55
4. test 0618082 14 50 55
5. admin 2 0 0

Sometimes it works sometimes it does not, frustrating!

In different scenerios, I am ranking the following way when ties occur. My code sometimes does this correctly but as you can see above it fails in certain areas, like it forgets R1.PSWins DESC, R1.PSPoints_Total DESC, R1.PSTBDifference....

Rank
1 Tied
1 Tied
3
4
5 Tied
5 Tied
5 Tied
8

etc...

The most frustrating thing is the sort order is correct, it is the rank sub query that fails under certain scenerious...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:48:09

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105292

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources