Thank you so much Vivian! Your first solution was exactly what I was looking for! It works perfectly! Thanks so much! Richard
Vivian Wang <[EMAIL PROTECTED]> wrote: create table temp select * from viewvisitor order by lastviewtime desc; select app, itemid, ownerid, visitorid, vusername,lastviewtime, sum(viewcount) AS totalcount, itemname from temp where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; or if you only care about max(lastviewtime), you can do this, select app, itemid, ownerid, visitorid, vusername, max(lastviewtime), sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; 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! > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
