I need help, very important: and a hard question

[Guest_imported]

i have the following situation.

a mysql database, with 2 tables: tbldisks and tblusage

in tbldisks there are about 54 rows with coloms like: diskid

in tlbusage there are about 300.000 rows with coloms like: diskusage,diskid and timestamp


now what i want is all the LATEST diskusages from every disk listed in those tables.

but the thing is i cant use sub-selects. becuzz mysql doesnt support that.

who can help me... i driving myself crazy for about 2 days on this.


thanks in advance,

grtz,
Alex

 

[karluk]

I think you want something like the following. See if it works in your database.

select a.diskid, a.diskusages from tblusage a,
(select diskid, max(timestamp) as most_recent
from diskusages
group by diskid) b
where a.diskid=b.diskid
and a.timestamp = b.most_recent [sig][/sig]

 

[MikeLacey]

To summarize activity over the last 24 hrs you could do something like:

select a.diskid, sum(b.diskusage)
from
tbldisks a,
tblusage b
where a.diskid = b.diskid
and b.timestamp >= today-1 /* the mysql equivalent */
group by b.diskusage
[sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]