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/
