SELECT query with sub ORDER BY query

[DaveEverFade]

I'm trying to run the following query but I'm getting confused:

 

SELECT teamid from recognition_teams order by (SELECT count(*) from recognition_scores where (team_1=teamid and team_1_score > team_2_score) or (team_2=teamid and team_2_score > team_1_score))

 

To explain what I'm trying to achive: I'm trying to get the teamid from one table but I want the order to be by the amount of wins in the scores table. So I want query to count the amount a team has a greater score than any other then order by the greatest...

 

Any ideas anyone? I've done something like this before but can't remember of find the file...

 

Ta

Dave

[shoz]

SELECT
r.teamid, IFNULL(w.num_wins, 0) AS wins
FROM
recognition AS r
LEFT JOIN
    (
    SELECT
    IF(team_1_score > team_2_score, team_1, IF(team_2_score > team_1_score, team_2), -1) AS teamid,
    COUNT(*) AS num_wins
    FROM
    recognition_scores
    GROUP BY
    teamid
    ) AS w
ON
r.teamid = w.teamid
ORDER BY
wins DESC

[fenway]

As shoz elegantly demonstrates (as always), try and "forget" that the outer query exists and write a normal-looking select statement to do the complicated stuff, and then treat this as a "new" table, and join it in as you would any other table.