Help Please - Rank - 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

SQL Server Development (2000)

Help Please - Rank

Author

Topic

adrox
Starting Member

17 Posts

Posted - 2005-09-02 : 00:15:30

Ok...I have a temp table that has the following results:

opmMemberID PtsAwarded leagueID tiebreaker
----------- ----------- ----------- -----------
7310 5 671549 24
7292 3 671549 24
7381 2 671549 20
7413 2 671549 0
7419 4 672454 15
7420 2 672635 12
7424 13 673359 0

What I need to do is rank the results by league

Like this

opmMemberID PtsAwarded leagueID tiebreaker RANK
----------- ----------- ----------- -----------
7310 5 671549 24 1
7292 3 671549 24 2
7381 2 671549 20 3
7413 2 671549 0 4
7419 4 672454 15 1
7420 2 672635 12 1
7424 13 673359 0 1

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 00:49:19

Try this

Select opmMemberID, PtsAwarded, leagueID, tiebreaker,
(Select count(*) from yourTable where leagueID =T.leagueID and opmMemberID <= T.opmMemberID) as Rank from yourTable T

Madhivanan

Failing to plan is Planning to fail

adrox
Starting Member

17 Posts

Posted - 2005-09-02 : 00:58:41

Thanks man. Worked great!

adrox
Starting Member

17 Posts

Posted - 2005-09-04 : 01:02:13

I thought this was working but it is not. Can you help me find the error.

SELECT m.opmMemberID, PtsAwarded, leagueID,
isNull((select tiebreaker
from opmMemberTiebreaker
where opmMemberID = m.opmMemberID
AND periodID = 1040
AND GameID = 1001),0) tiebreaker
into #rank
FROM opmMemberRank m
INNER JOIN opmLeagueMEmber lm ON lm.opmMemberID = m.opmMemberID
where m.periodID = 1040
Group by leagueID, m.opmMemberID, PtsAwarded
ORDER BY ptsAwarded desc, tiebreaker desc

Select opmMemberID, PtsAwarded, leagueID, tiebreaker,
(Select count(opmMemberID)
from #rank
where leagueID = T.leagueID and opmMemberID <= T.opmMemberID
) as Rank
INTO #rank2
from #rank T
Order by leagueID, PtsAwarded desc, tiebreaker, rank

select * from #rank2
where leagueID = 1183960

Which yieds these results. Notice that the rank is off:

opmMemberID PtsAwarded leagueID tiebreaker Rank
----------- ----------- ----------- ----------- -----------
12562 9 1183960 22 8
12511 6 1183960 50 2
12513 0 1183960 0 3
12520 0 1183960 0 4
12523 0 1183960 0 5
12524 0 1183960 0 6
12527 0 1183960 0 7
12707 0 1183960 0 9
12499 -12 1183960 0 1

So basically the temp table doesn't seem to be sorting correctly, thus when I rank it, it stores the rank incorrectly.

Any help would be great.

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 00:49:36

Try this

Select opmMemberID, PtsAwarded, leagueID, tiebreaker,
(Select count(opmMemberID)
from #rank
where tiebreaker = T.tiebreaker and opmMemberID <= T.opmMemberID
) as Rank
INTO #rank2
from #rank T
Order by leagueID, PtsAwarded desc, tiebreaker, rank

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources