GROUP BY returns grouped columns and aggregate functions, not rows. You
are grouping on history.person_id, so it makes no sense to select any
column that does not have a unique value for each history.person_id.
Indeed, other systems wouldn't even allow selecting non-grouped columns
<http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html>. Hence
your problem.
You want to select the rows where the history.time_sec is the most
recent (MAX) per group. That's essentially a 2-step process: first find
the max history.time_sec for each group, then select the rows which
match. You can either save the result of the first step in a temporary
table to use in the second step, or, if you have at least 4.1, you can
use a subquery to write it as one statement. The manual has examples,
as well as a 3rd, inefficient, 1-step method
<http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html>.
Michael
René Fournier wrote:
I'm trying to SELECT the most recent record in a table for each person
record in another table. Here's what I have so far:
SELECT
history.*,
persons.person_short_name,
persons.person_long_name
FROM
history, persons
WHERE
persons.id = history.person_id
AND persons.status = 1
GROUP BY history.person_id
ORDER BY history.time_sec DESC
The good thing: It retrieves DISTINCT persons (no duplicates).
The problem: The history rows are not the most recent for each person.
What I would need, theoretically, is for the ORDER BY clause to go
before GROUP BY, but MYSQL doesn't like that it seems.
Any ideas?
Thanks.
...René
---
René Fournier
www.renefournier.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
