sort by sub query

[bravo14]

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.

[requinix]

JOIN that heat table in and add the heat column as the last thing to sort by.

[bravo14]

The data is already coming from tbl_heat, and only need to sort it by this if everything else is the same, really struggling with this

[requinix]

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.

[bravo14]

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

[Barand]

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

[bravo14]

That seems to be right, thanks for your help