Jump to content

League position in MYSQL


bravo14
 Share

Recommended Posts

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.