List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:January 31 2006 4:50am
Subject:Re: Sort before grouping
View as plain text  
David Förster wrote:
> Hi,
> 
> is there any way to get datasets sorted before they're grouped by GROUP
> BY()?
> 
> I have a table of events at different locations and want to select the
> newest one for each location.
> 
> However "SELECT * FROM events GROUP BY location ORDER BY date DESC"
> gives me just some event per location and the result sorted by date.
> 
> Thanks in advance
> David
> 
> ps: please cc, I'm not on the list

You have a common misconception of what GROUP BY does.  GROUP BY does not ever
return rows from a table.  Instead, it returns group names and aggregate
statistics <http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html>
about groups.  That means that location is the only valid column you may select
when using "GROUP BY location", because it is the group name.  Many systems
won't even allow you to select columns not named in the GROUP BY clause.  MySQL
allows it as a convenience, but you are warned
<http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html> not to
select any column that does not have a unique value per group, because any row
from a given group may be used.

What you are trying to do is also a frequently asked question.  I see that Peter
Brawley has already sent you the link to the 5.0 manual page which provides a
solution using a subquery.  I'd suggest the 4.1 version of the page
<http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html>,
however, as it provides an additional solution which does not require a subquery
and is usually more efficient.

Michael


Thread
Sort before groupingDavid Förster30 Jan
  • Re: Sort before groupingPeter Brawley30 Jan
  • Re: Sort before groupingMichael Stassen31 Jan