SQL order by BUG?
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: +-+--+---+---+ | 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]
Re: SQL order by BUG?
Try to use 'avg_score desc,sum_score desc' Jørn Dahl-Stamnes [EMAIL PROTECTED] escreveu na mensagem news:[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: +-+--+---+---+ | 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]
Re: SQL order by BUG?
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]
Re: SQL order by BUG?
On Wednesday 06 September 2006 15:20, you wrote: the direction (desc/asc) order qualifier is per-sortfield and defaults to asc, so to get what you're after you'd want: order by avg_score desc, sum_score desc or order by avg_score desc, sum_score if you're after 'asc' on sum_score. - Rick Hmmm.. Seems like I have missed something important here. Thanks. -- 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]
