sql 2000 ranking with sub query

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

General SQL Server Forums

New to SQL Server Programming

sql 2000 ranking with sub query

Author

Topic

JSQLTECH94
Starting Member

11 Posts

Posted - 2008-06-21 : 12:02:01

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-21 : 17:38:53

Dup post:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources