Jump to content

sort by sub query


bravo14

Recommended Posts

Hi all

 

Going to try and explain this as best as I can.

 

I currently have a query that sorts a list of records by number of points, then wins, 2nd places, 3rd places etc, the query is as follows

 

 

SELECT  COUNT( `points` ) AS `rides`, SUM( `points` ) AS `pts`, SUM(IF(`points`=3,1,0)) AS `wins`, SUM(IF(`points`=2,1,0)) AS `2`,
SUM(IF(`points`=1,1,0)) AS `1`, SUM(IF(`points`=0,1,0)) AS `0`,  rider_name
FROM tbl_heat
WHERE card_id = 38
GROUP BY `rider_name` 
ORDER BY pts DESC, wins desc, `2` desc, `1` desc, `0` desc
LIMIT 8

 

this query has resulted in this result

 

'5','15','5','0','0','0','Jaroslaw Hampel'
'5','11','3','1','0','1','Greg Hancock'
'5','11','2','2','1','0','Andreas Jonsson'
'5','11','2','2','1','0','Matej Zagar'
'5','10','2','2','0','1','Chris Holder'
'5','9','1','3','0','1','Fredrik Lindgren'
'5','8','2','0','2','1','Krzystof Kasprzak'
'5','7','1','2','0','2','Tai Woffinden'
 
What I need to do is if all of these fields are the same for multiple rows I need to sort those by their result in the same heat.
 
I have a table called tbl_heat, and it contains the following fields
 
card_id, heat, rider_name, points
 
So where card_id and heat are the same I need to find which out of the riders on equal points etc scored the most in that heat.
 
for example in the example above Matej Zagar and Andreas Jonsson would be the other way round based on their heat result.
Hope that makes sense.
 
Let me know if you need me to clarify any of this, hard one to try and explain.
Link to post
Share on other sites

Ah, I thought you needed another join...

 

Right, and

ORDER BY pts DESC, wins desc, `2` desc, `1` desc, `0` desc, heat asc
(maybe heat desc, don't know the values)

will do exactly that. Sorting goes left to right in that list until the values don't match, so if pts, wins, 2, 1, and 0 all match then it'll end up sorting by heat; if they don't then heat doesn't matter because one of those others determined the sort order.

Link to post
Share on other sites

I have had a go and come up with the following query

 

 

SELECT  COUNT( `points` ) AS `rides`, SUM( `points` ) AS `pts`, SUM(IF(`points`=3,1,0)) AS `wins`, SUM(IF(`points`=2,1,0)) AS `2`,
SUM(IF(`points`=1,1,0)) AS `1`, SUM(IF(`points`=0,1,0)) AS `0`,  rider_name
FROM tbl_heat
WHERE card_id = 38
GROUP BY `rider_name` 
ORDER BY pts DESC, wins desc, `2` desc, `1` desc, `0` desc, (select * from tbl_heat a, tbl_heat b WHERE a.heat = b.heat AND a.card_id= 38 order by points desc)
LIMIT 8

 

However I am not getting any result at all

Link to post
Share on other sites

You cannot put a table subquery in the order by clause, only single value expressions.

 

From the MySQL manual

 


  • Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Bad idea to use those single digit names anyway, even if they they are quoted.

 

Is this what you want?

SELECT  COUNT( points ) AS rides
, SUM( a.points ) AS pts
, SUM(IF(a.points=3,1,0)) AS wins
, SUM(IF(a.points=2,1,0)) AS p2
, SUM(IF(a.points=1,1,0)) AS p1
, SUM(IF(a.points=0,1,0)) AS p0
, rider_name
, b.points as cardpoints
FROM tbl_heat a
    JOIN tbl_heat b USING (heat, card_id, rider_name)
WHERE a.card_id = 38
GROUP BY a.rider_name 
ORDER BY pts DESC, wins desc, p2 desc, p1 desc, p0 desc, cardpoints desc
LIMIT 8
Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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