Jose Miguel Pérez wrote:
Hi Vincent!
i'm trying to built a query that report me the latest tuple for a given field. I explain myself a bit. Let's say i have a table such this :
There is no simple solution to your problem. Strictly speaking, it's forbidden to name a column in the SELECT clause which is NOT on the GROUP BY clause. (Read chapter: "13.9.3 GROUP BY with Hidden Fields" from the MySQL Documentation.). Anyway, MySQL is relaxed from ANSI SQL in such a way it's permitted to include a column which is not in the GROUP BY clause (hidden columns). The values returned from such a query are "unpredictable".
I started saying there is no _simple_ solution. However, you can think a little bit and your problem goes away. I can think of two solutions to your problem which neither involves a subquery.
You are lucky in that you know for sure what your last value is (you do have a "date" field"). You already know the value of using MAX(date) in the SELECT. You can concatenate the date column with the version, and return a "SUBSTRING" from that operation, something like this:
SELECT SUBSTRING_INDEX(MAX(CONCAT(date, ' ', version)), ' ', -1) AS correct_version, location, version AS wrong_version FROM cities WHERE content='ALPHA' GROUP BY location
This is the MAX-CONCAT trick. It works, but it's inefficient, as it has to do a full table scan, with calculations done on each row. An index on date cannot be used in this case, because we are searching for the maximum CONCAT(date, ' ', version), rather than the maximum date.
You will get the following result set: (Notice the "correct_version" column).
+-----------------+----------+---------------+ | correct_version | location | wrong_version | +-----------------+----------+---------------+ | 10 | NEW-YORK | 11 | | 10 | PARIS | 10 | | 11 | TOKYO | 10 | +-----------------+----------+---------------+
There is another option, however, and this one is what I like most. In two words, you can join the table with itself, like this:
SELECT c1.date, c1.location, c1.version FROM cities c1 LEFT JOIN cities c2 ON c1.location=c2.location AND c1.content=c2.content AND c2.date>c1.date WHERE c2.id IS NULL AND c1.content = 'ALPHA'
What we have done here is first LEFT JOIN the table (cities in my example) with itself so that we have in the left part those rows which have the maximum date and with NULL on the right as the value of c2. We then select those saying "WHERE c2.id IS NULL". The last operation is selecting the rows for an 'ALPHA' content.
This will work, but it is also somewhat inefficient. The LEFT JOIN is creating numerous extra, unwanted rows, only to throw them away with the WHERE c2.id IS NULL. Assuming n rows for a particular location value, you are creating 1 + (n * (n - 1)/2) rows {optimized down to n rows, if id is defined NOT NULL} to find the one row you want for that group. That's no big deal for this small sample table, but it may not scale well.
Notice that with this version we also eliminate the GROUP BY, since all rows returned from the join are unique. The result set returned with the last example is this:
+------------+----------+---------+ | date | location | version | +------------+----------+---------+ | 2004-09-16 | PARIS | 10 | | 2004-09-16 | NEW-YORK | 10 | | 2004-09-15 | TOKYO | 11 | +------------+----------+---------+
The most efficient way is probably to use a temporary table.
CREATE TEMPORARY TABLE max_dates SELECT location, MAX(date) AS max_date FROM temp WHERE content = 'ALPHA' GROUP BY location;
SELECT t.* FROM temp t, max_dates m WHERE t.location = m.location AND t.date = m.max_date;
DROP TABLE max_dates;
The manual describes the MAX-CONCAT trick, the temporary table solution, and a subquery solution for 4.1+ <http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>.
Cheers,
Jose Miguel.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
