Multiple Group Bys?

[Garethp]

Hey guys, I'm having a little problem. I have three tables. sites, devices, and device_status. Basically, device_status holds multiple rows per device, of when the device last pinged on of our servers, and each site can have multiple sites.

 

So what I'm trying to do is find when a site was first created, but finding when it's devices first pinged us, and get all sites set up since October. What I tried was

 

SELECT sites.*, MIN(device_status) as firstcontact FROM sites

LEFT JOIN devices ON devices.siteid=sites.id

LEFT JOIN device_status on device_status.deviceid=devices.id

GROUP BY sites.id

HAVING

firstcontact > UNIX_TIMESTAMP('2010-08-01')

ORDER BY firstcontact DESC

 

But it shows no sites, and only two sites when I change it to January of 2010. My guess is that this is a result of the GROUP BY sites.id, and I was thinking that if I could somehow group by devices and sites it might show the correct results. How would I do this?

[fenway]

Sorry, I don't follow.

[kickstart]

Hi

 

You should use all the non aggregate fields in the SELECT within the GROUP BY. However MySQL is pretty tolerant of not doing this (most flavours of SQL would just error).

 

Is device_status really a column with a date in it?

 

All the best

 

Keith