Get the ranking of a certain column in one query

[ninedoors]

I am trying to find the rank for a player in my hockey league in goals.  I have the goals stored so that each row is a goal with a user id in it as well as a team id.

 

What I would like to do is be able to put in userid = 3 and get that users rank in the league in goals.  I came across a solution in google but don't understand it completely as I haven't been using mySQL that long and don't understand how to use dervived tables from a query.

 

Here is the code they used to get a score from a game which is similar to my problem:

 

        'SET @rownum := 0;

        SELECT id_member, rank 
        FROM (
        SELECT @rownum := @rownum+1 AS rank, id_member, team_id, SUM(IF(action_id = 1, 1, 0)) as goals FROM smf_osm_scoring
       GROUP BY id_member 
       ORDER BY goals DESC
        ) AS derived_table WHERE id_member = 1;'

 

Any help or direction would be great, even if it is read this book, as I would love to be able to understand this concept rather than just have it done for me.

 

Nick

[fenway]

See here.