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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-22 : 09:03:15
|
Dave writes "I finally found your artice on retrieving result set with row number but I'm still having problems when applying it to a more intense query. I am going to try to be as clear as possible. thanks in advance
I have 2 tables
member and score_playlist with the following columns
member
1)member_id pk
score_playlist
1)member_id fk to member.member_id
2)playlist_id
3)score
the following query retrieves a sum of all the max(scores) across unique playlists for a member
the returning table would look like this
sum_score login member_id
14000 'David' 23455
I need to know the rank of that member
I know to retrieve info of all unique members info I comment out the AND member_id=23455 line
but i still dont know how to get their ranks and subsequently how to retrieve my member's rownums(rank) Again I thank you for your help
SELECT SUM(t2.score1) sum_score, m.login, t2.mid
FROM member m,
(
SELECT playlist_id pid, member_id mid, MAX(score) score1
FROM score_playlist
GROUP BY playlist_id , member_id
) t2
WHERE t2.mid = m.member_id
and m.member_id=23445
GROUP BY t2.mid,m.login
order by 1 desc;" |
|
|
|
|
|
retrieving row number continuation