Rank MySQL results after joining with another table, then filtering them by a condition

[Nathan Warden]

I found how to rank results, but have yet to find a solution for how to rank them after they've been filtered by a condition. What I need to do is:

1) Sort scores in descending order
2) Join the results with the account table
3) Filter the results by age and gender (from the account table)
4) Rank them
5) Grab the results within a range of ranks (IE: 25-75, but I put 0-10 for simplicity below)

Each solution I've found either doesn't properly rank them, or ranks them and then filters the results. However, this leaves me with ranks like 1, 3, 4, 6, 10, etc when I want 1, 2, 3, 4, 5, etc. or 25, 26, 27, 28, 29, etc.

Here is what I have right now:

SELECT a.id, a.username, a.country, score, user_id, duration, rank FROM (
SELECT s.*, a.age, a.gender, @rownum := @rownum + 1 AS rank FROM scores s,
(SELECT @rownum := 0) r LEFT JOIN accounts a ON 1
WHERE a.age>=18 && a.age<=35 && a.gender='m' ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id WHERE rank >= 0 && rank <= 10 && a.age>=18 && a.age<=35 && a.gender='m'

The problem with the above query is that it gives me correct rankings without any gaps, but the scores are completely out of order. IE. 5, 1, 10, 7, 125, 50, etc. IE. They come out in the same order they are in the database, unsorted.

This is what my previous query looked like:

SELECT a.id, a.username, a.country, score, duration, rank FROM ( SELECT s.*, @rownum := @rownum + 1 AS rank FROM scores s,
(SELECT @rownum := 0) r ORDER BY score DESC, duration DESC, time ) `selection`
LEFT JOIN accounts a ON a.id=user_id WHERE rank >= 0 && rank <= 10 && a.age>=18 && a.age<=35 && a.gender='m'

This query gives me properly sorted scores, but with ranks like 3, 5, 8, 9, 11 instead of 1, 2, 3, 4, 5.

Here is some real-world sample data...

The first query from above with proper rankings, but incorrectly sorted scores (IE. the same order as they were added to the database):

score | rank
-------------
5 | 1
1 | 2
22 | 3
13 | 4
23 | 5
23 | 6
34 | 7
32 | 8
58 | 9
76 | 10


The second query from above, properly sorted, but ranks still take other users into consideration when ranking, then get excluded in the final result:

score | rank
-------------
76 | 3 --- This should have started at 1
62 | 4
58 | 5
42 | 7 --- Notice 6 was skipped
34 | 8
32 | 9
29 | 10

What I expect:


score | rank
-------------
76 | 1
62 | 2
58 | 3
42 | 4
34 | 5
32 | 6
29 | 7

Thanks for any help!