How to access the last entry for different systems

[aks2trip]

Hi,
How would I access the values of the last datetime stamp for different system id's present in the table?

For example, if i have 5 system id's their last entered datetime stamps might be different. How would i access the related values for the systems?

Thanks for all help in advance.

 

[TonyGroves]

Do you mean you want the highest timestamp value for each system-id? If so, you could use:
[tt]
SELECT systemid,MAX(timestampfield)
FROM tablename
GROUP BY systemid
[/tt]

 

[aks2trip]

Here is what i tried - the table contains different systems and the different volumes attached to it.

SELECT systems.HOSTNAME, volinfo.SYS_ID, volinfo.VOL_ID, volinfo.MOUNT, max(volcap.sampletime),volcap.KB_USED, volcap.KB_TOTAL FROM (volinfo INNER JOIN systems ON volinfo.SYS_ID = systems.SYS_ID) INNER JOIN volcap ON (volinfo.VOL_ID = volcap.VOL_ID) AND (volinfo.SYS_ID = volcap.SYS_ID) GROUP by volinfo.sys_id, volinfo.vol_id;


This ends up giving me the wrong value of kb_used. It seems when i use the group function, it groups certain values, not too sure what exactly.

Running the exact same command without the max and group function gives me the correct value but as soon as i add that, it returns a wrong value.

Is there anyway i can just retrive the last value in the list? or how else can i get the right value? Why isnt max returning the correct value?

Any ideas?
Thanks

 

[r937]

blame mysql!

because it allows invalid GROUP BY queries to run (see GROUP BY with Hidden Fields), many mysql developers are never taught how to use the GROUP BY clause properly

try this -- make sure every non-aggregate column in your SELECT list is also in the GROUP BY and vice-versa

add and remove columns until you get results that make sense

you may, however, not get any results that you can use, if you are joining several unrelated (unrelated to each other) one-to-many relationships in the same query

for example, let's say we had a table of people --

John
Mary
Fred

and we want to join it to a table of pets --

John|dog
John|bird
John|fish
Mary|cat
Fred|lizard

and we also want to join the people table to a table of cars --

John|Ford
John|Toyota
Mary|VW
Fred|Hyundai

then if we write a query to join people to both pets and cars, there will be cross join effects, and John will have 6 rows

r937.com | rudy.ca