2006/9/6, Jørn Dahl-Stamnes <[EMAIL PROTECTED]>:
I get the following query:
select rt.team_id,count(p.race_id) as cnt,sum(p.points) as
sum_score,sum(p.points) / count(p.race_id) as avg_score from <some tables,
joins and where-criterias> group by rt.team_id order by avg_score desc;
which gives:
+---------+------+-----------+-----------+
| team_id | cnt | sum_score | avg_score |
+---------+------+-----------+-----------+
| 163 | 3 | 4.5 | 1.500 |
| 312 | 6 | 9.0 | 1.500 |
| 223 | 2 | 3.0 | 1.500 |
| 167 | 1 | 1.5 | 1.500 |
....
But if I change to order by from 'avg_score desc' to 'avg_score,sum_score
desc' I get:
since the default ordering is ASC and that the comma operator has
precedence over DESC, your query is interpreted as :
select rt.team_id,count(p.race_id) as cnt,sum(p.points) as
sum_score,sum(p.points) / count(p.race_id) as avg_score from <some tables,
joins and where-criterias> group by rt.team_id order by avg_score
ASC,sum_score DESC;
+---------+------+-----------+-----------+
| team_id | cnt | sum_score | avg_score |
+---------+------+-----------+-----------+
| 262 | 9 | 9.0 | 1.000 |
| 161 | 7 | 7.0 | 1.000 |
| 317 | 2 | 2.0 | 1.000 |
| 97 | 1 | 1.0 | 1.000 |
| 143 | 1 | 1.0 | 1.000 |
....
The order has changed from desc to asc!
Looks like a bug for me or am I blind? ;-)
select version();
+----------------+
| version() |
+----------------+
| 4.1.8-standard |
+----------------+
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
http://www.myspace.com/sakuradrop : forget the rest
http://www.w-fenec.org/ Webzine rock/metal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
