I am using the following sql statement to generate a rank for a number of scores:
SELECT A.MemberID, A.FirstName, A.Surname, A.Sex, A.State, A.Region, A.Points, A.Matches, COUNT(B.points) + 1 AS rank
FROM tbl_players A LEFT OUTER JOIN tbl_players B ON (B.Points > A.Points)
GROUP BY MemberID
This works perfectly and gives me the correct ranks eg:
memberID first Name surname sex state region points rank
001 bob jones male NSW xxxxx 500 1
001 jack jones male QLD xxxxx 450 2
001 jill jones female VIC xxxxx 400 3
001 mike jones male NSW xxxxx 350 4
001 lee jones male NSW xxxxx 300 5
However I also want to assign ranks to scores when the records are filtered by state. eg:
SELECT A.MemberID, A.FirstName, A.Surname, A.Sex, A.State, A.Region, A.Points, A.Matches, COUNT(B.points) + 1 AS rank
FROM tbl_players A LEFT OUTER JOIN tbl_players B ON (B.Points > A.Points)
WHERE A.State = 'NSW
GROUP BY MemberID
This returns:
memberID first Name surname sex state region points rank
001 bob jones male NSW xxxxx 500 1
001 mike jones male NSW xxxxx 350 4
001 lee jones male NSW xxxxx 300 5
ie. the rank is now for all states and not just for NSW.
Is there another way to write my code so that even when I filter the query the rank will number like: 1,2,3,4,5 etc. (ie. I need ranks for all states which works fine but also need to have ranks for each state). Hope this makes sense.
