List:General Discussion« Previous MessageNext Message »
From:Olexandr Melnyk Date:March 17 2007 7:56pm
Subject:Re: Group By / Order BY
View as plain text  
1) ORDER BY is executed after GROUP BY;
2) In ORDER BY don't use columns that aren't in GROUP BY, unless it's an
aggregated value;

Your query can be rewritten as:

select date
     , max(rev) as max_rev
  from table
group
    by date
order
    by max_rev desc

2007/3/17, Justin <mysql@stripped>:
>
> I've got an issue with group / order by.. here's what I'm wondering..
>
> simple table..
> date         |         rev
> ----------------------
> 20070315          1
> 20070315          2
> 20070316          1
> 20070316          2
>
> Query I'm running
> SELECT * FROM `table` GROUP BY `date` order by `rev` DESC
>
> I would think this would return
> 20070315 - 2
> 20070316 - 2
>
> but it doesn't it returns
>
> 20070315 - 1
> 20070316 - 1
>
> What am I missing?  I'm trying to do a group by the date and return the
> higher rev..
>
> thanks agian!
>
> Justin.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
---------------------------------
Olexandr Melnyk,
http://omelnyk.net/

Thread
Group By / Order BYJustin17 Mar
  • Re: Group By / Order BYOlexandr Melnyk17 Mar
Re: Group By / Order BYJustin17 Mar
  • Re: Group By / Order BYPeter Brawley17 Mar