473,881 Members | 1,725 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(date timestamp) as datetimestamp,l atitude,longitu de
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.no deid AND tl.datetimestam p < t2.datetimestam p
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 1663
JimmyHoffa wrote:
SELECT tl.*
FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.no deid AND tl.datetimestam p < t2.datetimestam p
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.no deid AND tl.datetimestam p < t2.datetimestam p
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.no deid AND tl.datetimestam p < t2.datetimestam p
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.no deid AND tl.datetimestam p < t2.datetimestam p
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,row s,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
13593
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 take about 3 mins to return. The explain plan for the query with the order by clause is as follows (EXP2). The explain plan 2 shows that there is no changes to the index selection except adding the "Sort Order by" step. The total number of...
2
5431
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` ) FROM `table` WHERE 1 GROUP BY `subject` ORDER BY `datetime` DESC LIMIT 0 , 30
16
2088
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: 'tblContact' and 'tblCategory' where categories are like: Code Name 010101 Short 010102 Fat
4
2305
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' Help me to sort this out, it has taken me almost a week to wade through and it still won't work. Select Case Forms("frmForce").OpenArgs
11
3910
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 could I sort it as: Low, Medium, High? Any suggestion is greatly appreciated, James
2
3360
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 that column. I even want to sort up and down if one clicks again on the same header. No problem so far, all works well for one column. Now I want to sort on the first choosen column ASC or DESC and additionally on a second column ASC. I use the...
2
2312
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 word=0; char *querystring; querystring=malloc(sizeof(char)*100000); if (getenv("QUERY_STRING")==NULL)
19
1481
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 date system). Same goes for sorting datagrid (sorts dates and decimal numbers - ie 31.12.2004 is sorted by first number instead of second and 100.000,00 is treated as text ). Is there a simple way of changing this or am I to write my own sorting...
104
10923
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 sorted by Numb. Everything I've read on the web suggests that including the TOP directive should enable ORDERY BY in views. Does someone have an idea why the sorting is not working correctly for this particular view? thanks. CREATE VIEW...
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10399
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.