473,322 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

stupid order by and group by problem..

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..

Nov 25 '05 #1
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;
Nov 25 '05 #2
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,
Nov 25 '05 #3
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.
Nov 25 '05 #4
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..
Nov 25 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
2
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` )...
16
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:...
4
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' ...
11
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...
2
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...
2
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...
19
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...
104
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.