That will not work. I show you an example:
Table zz:
- +----+------------+------+
-
| id | arrival | name |
-
+----+------------+------+
-
| 1 | 2006-12-31 | John |
-
| 2 | 2007-01-01 | John |
-
| 3 | 2007-01-12 | John |
-
| 4 | 2006-12-21 | Pete |
-
| 5 | 2007-01-10 | Pete |
-
| 6 | 2006-01-12 | Pete |
-
| 7 | 2006-06-16 | Mark |
-
+----+------------+------+
Executing your statement
- SELECT max(id),arrival,name FROM zz
-
GROUP BY name ORDER BY name,arrival;
produces the following result:
-
+---------+------------+------+
-
| max(id) | arrival | name |
-
+---------+------------+------+
-
| 3 | 2006-12-31 | John |
-
| 7 | 2006-06-16 | Mark |
-
| 6 | 2006-12-21 | Pete |
-
+---------+------------+------+
There is no connection between the max(id) and the row content. MAX() is only executed AFTER a row has been read.
So, for your single statement solution you must use a subquery in the WHERE clause that returns the maximum id value per name, as in:
- SELECT id as highest,arrival,name FROM zz
-
WHERE id IN (SELECT MAX(id) FROM zz GROUP BY name);
That statement produces the required result:
- +---------+------------+------+
-
| highest | arrival | name |
-
+---------+------------+------+
-
| 3 | 2007-01-12 | John |
-
| 6 | 2006-01-12 | Pete |
-
| 7 | 2006-06-16 | Mark |
-
+---------+------------+------+
Ronald :cool: