Jump to content

League position in MYSQL


bravo14

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. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.