Ranking in SQL Server 2000

[JSQLTECH94]

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!

Thanks!

 

[SQLSister]

first get rid of select top 100 percent, completely unneeded. I cringe every single time I see this.

Now on to your question, please post sample data and results you would like to see. Explain what goes wrong when you order by more than column.

"NOTHING is more important in a database than integrity." ESquared

 

[JSQLTECH94]

I removed the top 100 percent!

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!

Let me know if I can provide anything else and thanks for your insight in advance...

 

[JSQLTECH94]

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...

 

[JSQLTECH94]

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