bruce wrote:
hi...
i have the following select... SELECT itemID, process, status, tblType, MAX(date) FROM historyTBL WHERE (tblType = '3' or tblType = '2') GROUP BY tblType;
it seems to work, in that it gives me the rows for the two types that i'm grouping by.
the problem is that i want the row that contains the max date. the query is only returning the max date, with the other elements in the row coming from who knows where in the table.
any idea on how i can achieve the complete row containing the max date???
i've looked through google/mysql with no luck....
thanks.
-bruce
This is a frequently asked question. It seems natural to expect that this query should return the row with the maximum date column value, but that's not how GROUP BY works. What if 2 rows both have the maximum value of date? Which should be returned? Now, consider the following query:
SELECT tblType, MAX(date), MIN(date), AVG(date) FROM historyTBL GROUP BY tblType;
Which row should be returned now? You see? The aggregate functions return descriptive statistics about the data in each group, not rows from the table.
Many systems would not even allow a query such as yours, in which columns (rather than aggregate functions) which are not grouped are selected. MySQL allows this as a convenience to save typing, but you are warned <http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html> not to include columns whose values are not unique per group. If you do, you get essentially random data. (Actually, I think in the interest of speed you get the values in the first row found per group).
So, how do you solve the problem? Essentially, you must look at the table twice. First you get the max value for each group, then you look again to find the rows which have those values. You can achieve that with 2 queries and a temporary table, or with one query with a subquery if you have mysql 4.1. There is also a trick where you append the extra columns to the date, find the maximum value of that, and break the maximum back into pieces -- the MAX-CONCAT trick.
I'm not sure why you started a new thread on this, but all 3 methods are explained in the manual page I referenced in my earlier reply <http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>. For example,
LOCK TABLES historyTBL READ;
CREATE TEMPORARY TABLE maxdates SELECT tblType, MAX(date) mdate FROM historyTBL GROUP BY tblType;
SELECT h.itemID, h.process, h.status, h.tblType, h.date AS Max_Date FROM historyTBL h, maxdates m WHERE h.tblType = m.tblType AND h.date = m.mdate;
UNLOCK TABLES; DROP TABLE maxdates;
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
