Re: Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?

[Vivian Wang]

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! 
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]