order by and group by problem..

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Hi hoping someone might be able to help with this issue,

The following query is one i'd love to be able to use, but cant...

select nodeid,max(datetimestamp) as datetimestamp,latitude,longitude
from trackinglogs
group by nodeid
order by datetimestamp desc;

I'm trying to get the latest rows according to datetimestamp column,
for each unique nodeid in my table..  however the above query works to
an extent but the lat & long columns dont correspond with the same row
as the datetimestamp..

So my alternative was this...

FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.nodeid AND tl.datetimestamp < t2.datetimestamp
WHERE t2.nodeid IS NULL;

which produces the correct results but is slow, taking 2-3 seconds on a
table with 1200 rows, which will be growing considerably over time..

So i'm stuck at what to do really..

Any thoughts/advice would be really appreciated.


Site Timeline