Here's how I handle these situations. If I need all of the data from the
row that contains the maximum of an unindexed column:
SELECT @maxval := MAX(column_name) FROM tablename;
SELECT * FROM tablename WHERE column_name = @maxval;
For the same thing but for an indexed column
SELECT * FROM tablename ORDER BY column_name DESC LIMIT1;
To get all of the rows that contain the MAX() value of column2 for all
values of column1, I need to use a temporary table:
CREATE TEMPORARY TABLE tmpMax
SELECT column1, MAX(column2) as maxval
FROM tablename
GROUP BY column1;
SELECT *
FROM tablename t
INNER JOIN tmpMax tm
ON tm.column1 = t.column1
AND tm.column2 = t.column2;
Now for your case. You need to update all of the rows that have the most
recent dateposted for each office value.
CREATE TEMPORARY TABLE tmpUpdateMe
SELECT office, MAX(dateposted) as maxdate
FROM dCOPY
GROUP BY office;
UPDATE dCOPY d
INNER JOIN tmpUpdateMe tum
ON tum.office = d.office
AND tum.maxdate = d.dateposted
SET status = 1;
You should only need to wrap the 1 with quotes (like '1') if you are
trying to insert it into a character-based field. You do not need quotes
on any numeric value being assigned to a numeric column.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Louie Miranda <[EMAIL PROTECTED]> wrote on 07/27/2004 03:04:27 AM:
> Just recently post a problem on how can i see all max(dateposted) on
> all of my records by doing this..
>
> select datacount,office,filename,status, max(dateposted) from dCOPY
> group by office;
>
> now, i was wondering if i can use max() on update to update all my
> current records only..
>
> i tried this:
>
> mysql> update dCOPY set status = '1' where max(dateposted);
> ERROR 1111: Invalid use of group function
> mysql>
>
> But as you can see, it returns an error for an invalid group function.
>
> --
> Louie Miranda
> http://www.axishift.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
