I think the suggestion posted here... http://lists.mysql.com/mysql/182424
should get you going in the right direction. You really need to know what you are doing to know if it is giving you the correct answer or not. It would be cool if their was something like a GROUP_ROW(cols, expr) to do what you want explicity. SELECT * FROM chi; +----------+----+------+ | child_id | id | type | +----------+----+------+ | 1 | 1 | 0 | | 2 | 1 | 0 | | 3 | 1 | 1 | | 4 | 1 | 2 | | 5 | 2 | 2 | +----------+----+------+ -- Totally fake and probably silly SQL... SELECT id, group_row(child_id,type,child_id=min(child_id)) FROM chi2 GROUP BY id; +----------+----+------+ | child_id | id | type | +----------+----+------+ | 1 | 1 | 0 | | 5 | 2 | 2 | +----------+----+------+ or GROUP_ROW(cols,sortby=) -- More sillyness SELECT id, group_row(child_id,type,sortby=rand()) FROM chi2 GROUP BY id; Just an idea. Dan. On Mon, 11 Apr 2005, Rich Carr wrote: >Is there a way to set which rows values are used by the GROUP BY clause >for the fields that are not in the GROUP BY clause? > In this following select statement the group by and order work but the >value of the lastviewtime field is not the value of the most recent >datetime row. Is there any way to modify the select statement so that >the returned lastviewtime field will have the most recent datetime? > > select app, itemid, ownerid, visitorid, vusername, lastviewtime, >sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = >2 GROUP BY concat( app, itemid ) ORDER BY totalcount > > >create table viewvisitor >( > app char(15), > itemid INT UNSIGNED NOT NULL, > ownerid INT UNSIGNED NOT NULL, > ousername varchar(25), > visitorid INT UNSIGNED NOT NULL, > vusername varchar(25), > vfullname varchar(70), > lastviewtime DATETIME NOT NULL, > viewcount INT, > itemname VARCHAR(40), > > > PRIMARY KEY master(app, visitorid, itemid), > INDEX (ownerid), > INDEX (lastviewtime), > INDEX (viewcount) >); > >Thanks!!! > >Rich > > > >--------------------------------- >Do you Yahoo!? > Yahoo! Small Business - Try our new resources site! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
