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...
SELECT tl.*
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.
Thanks.. 4 1638
JimmyHoffa wrote: SELECT tl.* 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..
Do you have index in nodeid? What does this query print out:
EXPLAIN SELECT tl.*
FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.nodeid AND tl.datetimestamp < t2.datetimestamp
WHERE t2.nodeid IS NULL;
Aggro wrote: JimmyHoffa wrote:
SELECT tl.* 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..
Do you have index in nodeid? What does this query print out:
EXPLAIN SELECT tl.* FROM trackinglogs tl LEFT JOIN trackinglogs AS t2 ON tl.nodeid=t2.nodeid AND tl.datetimestamp < t2.datetimestamp WHERE t2.nodeid IS NULL;
Yeah I have indexes on nodeid and datetimestamp.. the explain gives me...
id,select type,table,type,possible keys,key,key len,ref,ref,rows,extra
1,SIMPLE,tl,ALL,NULL,NULL,NULL,NULL,1175,
1,SIMPLE,t2,ref,DateTimeStamp
Thanks,
JimmyHoffa wrote: 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..
If you have MySQL >= 4.1 you can try this subquery instead.
SELECT trackinglogs.*
FROM trackinglogs,
(SELECT nodeid AS A, MAX( datetimestamp ) AS P
FROM trackinglogs
GROUP BY nodeid) AS maxdates
WHERE nodeid = A AND datetimestamp = P;
Above example is a modified version, from here: http://dev.mysql.com/doc/refman/5.0/...group-row.html
I'm afraid there are no pretty ways to do this without subqueries. But
you can try something where you make two or more queries. First get
nodeid and max dates. Then run another query where you search only rows
where
(nodeid=a AND datetimestamp=b) OR (nodeid=c AND datetimestamp=d) OR ...
Where a,b,c and d are results from the first query.
Btw. Are you sure that there can't be two items with the same node id
and the same datetimestamp? Or does it matter if that happens.
Aggro wrote: JimmyHoffa wrote:
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..
If you have MySQL >= 4.1 you can try this subquery instead.
SELECT trackinglogs.* FROM trackinglogs, (SELECT nodeid AS A, MAX( datetimestamp ) AS P FROM trackinglogs GROUP BY nodeid) AS maxdates WHERE nodeid = A AND datetimestamp = P;
Above example is a modified version, from here: http://dev.mysql.com/doc/refman/5.0/...group-row.html I'm afraid there are no pretty ways to do this without subqueries. But you can try something where you make two or more queries. First get nodeid and max dates. Then run another query where you search only rows where
(nodeid=a AND datetimestamp=b) OR (nodeid=c AND datetimestamp=d) OR ...
Where a,b,c and d are results from the first query.
Btw. Are you sure that there can't be two items with the same node id and the same datetimestamp? Or does it matter if that happens.
Brilliant from 2500ms to 62ms.. Thanks so much :-)
Nah they'll never be two nodes with the same datetime..
A Happy dave.. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Scott |
last post by:
I have a view that contains the following SQL1 and it takes about 500ms
to run with Explain Plan EXP1. However, if I add an order by
clause(e.g. select * from view1 order by ID desc), it will then...
|
by: Experienced but Undocumented |
last post by:
Here's another one; thanks so much to everyone who helped with my last
problem. I can't upgrade mySQL but I figured out a way around it from your
examples.
SELECT `subject` , max( `datetime` )...
|
by: Justin Hoffman |
last post by:
This is a question concerning query optimisation. Sorry if it's a bit long,
but thanks to anyone who has the patience to help - This is my first post
here...
If I have two tables:...
|
by: Mark |
last post by:
the Following bit of code doesn't work.
It seems to respond to the second, starting with 'add iif statement
for Good Practice', but not to the first, starting 'add iif statement
for archived'
...
|
by: James P. |
last post by:
Hello,
I have a report with the Priority field is used as sort order and
grouping. The problem is the data in this Priority field if sorted in
ascending order is: High, Low, and Medium. How...
|
by: Dutchy |
last post by:
Hi there,
After spending several hours trying all I could imagine and search for in
Google I gave up. In a continuous form I want to sort the choosen column by
clicking the header (label) of...
|
by: Lampa Dario |
last post by:
Hi, where is this stupid error in this program? When I execute it, i
receive a segmentation fault error.
#include <stdio.h>
int main(int argc, char *argv, char *env) { int i=0; int l=0;
int...
|
by: Sput |
last post by:
I am thoroughly frustrated with these seemingly unchangable settings in C#.
For example, fetching data from database sorted by date returns wrong order
(would be correct if I was using american...
|
by: Beowulf |
last post by:
I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |