MySQL ODBC Connector - Sort before grouping

Author

Sort before grouping

David Förster

2006-01-30, 9:25 am

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2006-01-30, 11:24 am

David,

>I have a table of events at different locations and want to select the
>newest one for each location.

Famous & oft-asked, how to retrieve a groupwise-max values. See
http://dev.mysql.com/doc/refman/5.0...-group-row.html

PB

-----

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
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.23/243 - Release Date: 1/27/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Michael Stassen

2006-01-31, 3:23 am

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...-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...den-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...-group-row.html>,
however, as it provides an additional solution which does not require a subquery
and is usually more efficient.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links