League position in MYSQL

[bravo14]

I have a query at the moment that gives a name, number of rides and number of points

 

 

SELECT  COUNT( `points` ) AS 'rides', SUM( `points` ) AS 'pts', rider_name
FROM tbl_heat
WHERE card_id = $card
GROUP BY `rider_name`
ORDER BY pts DESC

 

what I want to do now is add the position in a league table, I have tried amending the query and have the following

 

 

SELECT @serial := @serial +1 AS pos, rider_name
FROM(
SELECT  COUNT( `points` ) AS 'rides', SUM( `points` ) AS 'pts', rider_name
FROM tbl_heat
JOIN (

SELECT @serial :=0
) AS init
WHERE card_id = $card
GROUP BY `rider_name`
ORDER BY pts DESC
) as sorted

 

however this query doesn't give what I am after, not sure how far off getting it right, any help would be great

[Barand]

try

SELECT 
    rider_name
    , rides
    , pts
    , pos
FROM
    (
    SELECT  
        COUNT( `points` ) AS 'rides'
        , SUM( `points` ) AS 'pts'
        , rider_name
        , @serial := @serial +1 AS pos
    FROM tbl_heat
        JOIN (SELECT @serial :=0) AS init
    WHERE card_id = $card
    GROUP BY `rider_name`
    ORDER BY pts DESC
    ) as sorted

[bravo14]

I have tried the query above and get the following result

 

rider_name  rides                  pts  pos  Troy Batchelor 7 20 16 Niels-Kristian Iversen 7 16 9 Peter Kildemand 7 15 1 Greg Hancock 7 11 13 Jaroslaw Hampel 6 10 14 Darcy Ward 6 9 8 Tai Woffinden 6 9 11 Michael Jepsen Jensen 6 8 6 Krzysztof Kasprzak 5 7 10 Matej Zagar 5 7 3 Andreas Jonsson 5 6 5 Nicki Pedersen 5 5 2 Martin Smolinski 5 5 15 Fredrik Lindgren 5 4 4 Kenneth Bjerre 5 3 12 Chris Harris 5 3 7 Mikkel Michelsen 1 0

17

 

The pos field should be the order as the rows are displayed above so Troy Batchelor 1, Niels Kristian Iversen 2 etc etc

[Barand]

Sorry!

SELECT 
    rider_name
    , rides
    , pts
    , @serial := @serial +1 AS pos
FROM
    (
    SELECT  
        COUNT( `points` ) AS 'rides'
        , SUM( `points` ) AS 'pts'
        , rider_name
    FROM tbl_heat
        JOIN (SELECT @serial :=0) AS init
    WHERE card_id = $card
    GROUP BY `rider_name`
    ORDER BY pts DESC
    ) as sorted