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

[Peter Brawley]

Many problems with that query, eg the mixing of comma join and explicit join syntax, and the absurdity of left joining on 1 then adding Where conditions on the joined table, effectively turning the left join into a cross join with conditions, logical mayhem.

...FROM scores s, (SELECT @rownum := 0) r LEFT JOIN accounts a ON 1 WHERE...

As the manual says, don't mix comma join and explicit join syntax, such syntactic confusion can produce anomalous results. Use explicit join syntax throughout---it's guaranteed to behave correctly, easier to write, easier to debug, easier to maintain.

Without a built-in func like MSSQL's Rank(), it's best to start by precomputing rank in a tmp table ...

drop temporary table if exists score_ranked;
set @i=0;
create temporary table score_ranked
select user_id, score, @i:=@i+1 as rank
from scores
order by score desc;

Once the whole query is debugged, it might be possible to optimise away the tmp table. Meanwhile, getting the full result you need by joining with an accounts table and filtering is straightforward, eg ...

select a.id, a.username, a.country, s.score, s.rank 
from score_ranked s
join accounts     a on a.id=s.user_id
where a.age between 18 and 35
  and a.gender='m'
  and s.rank between 0 and 10
order by s.score desc;