mysql - Using max function, group by and join -

i have 3 tables:

create table if not exists `disksinfo` (   `idx` int(10) not null auto_increment,   `hostinfo_idx` int(10) default null,   `id` char(30) default null,   `name` char(30) default null,   `size` bigint(20) default null,   `freespace` bigint(20) default null,   primary key (`idx`) )  create table if not exists `hostinfo` (   `idx` int(10) not null auto_increment,   `host_idx` int(11) default null,   `probetime` datetime default null,   `processor_load` tinyint(4) default null,   `memory_total` bigint(20) default null,   `memory_free` bigint(20) default null,   primary key (`idx`) )  create table if not exists `hosts` (   `idx` int(10) not null auto_increment,   `name` char(30) default '0',   primary key (`idx`) )  

basicaly, hosts ist fixed list of hostnames used in hostinfo table (hostinfo.host_idx = hosts.idx) hostinfo table filled each few minutes data hosts , in addition, each hostinfo row @ least 1 diskinfo row created. each diskinfo row contains informations @ least 1 disk (so, hosts there 3-4 rows of diskinfo). diskinfo.hostinfo_idx = hostinfo.idx. hostinfo.probetime time @ data snapshot created.

what want perform select last hostinfo (.probetime) each particular distinct host (hostinfo.host_idx), while joing informations disks (diskinfo table) , host names (hosts table)

i came this:

select hinfo.idx,        hinfo.host_idx,        hinfo.processor_load,        hinfo.memory_total,        hinfo.memory_free,        hnames.idx,        hnames.name,        disks.hostinfo_idx,        disks.id,        disks.name,        disks.size,        disks.freespace,        max(hinfo.probetime)   systeminfo.hostinfo hinfo        inner join systeminfo.hosts hnames                on hnames.idx = hinfo.host_idx        inner join systeminfo.disksinfo disks                on disks.hostinfo_idx = hinfo.idx group  disks.id,           hnames.name order  hnames.name,           disks.id   

it seems work! but, 100% correct? optimal? tip!

it's not 100% correct, no.

suppose have table:

x   |   y   |   z -----------------       b       1       c       2 d       e       1 d       f       2 

now when group x, rows collapsing , mysql picks random row collapsed ones. might get

x   |   y   |   z -----------------       b       2 d       e       2 

or this

x   |   y   |   z -----------------       c       2 d       f       2 

or combination, not determined. each time fire query might different result. 2 in column z there, because of max() function, won't corresponding row it.

other rdbmss same, forbid default (in can forbidden in mysql, too). have 2 possibilities fix (actually there more, i'll restrict two).

either put columns have in select clause not used in aggregate function sum() or max() or whatever group by clause well, this:

select hinfo.idx,        hinfo.host_idx,        hinfo.processor_load,        hinfo.memory_total,        hinfo.memory_free,        hnames.idx,        hnames.name,        disks.hostinfo_idx,        disks.id,        disks.name,        disks.size,        disks.freespace,        max(hinfo.probetime)   systeminfo.hostinfo hinfo        inner join systeminfo.hosts hnames                on hnames.idx = hinfo.host_idx        inner join systeminfo.disksinfo disks                on disks.hostinfo_idx = hinfo.idx group          hinfo.idx,        hinfo.host_idx,        hinfo.processor_load,        hinfo.memory_total,        hinfo.memory_free,        hnames.idx,        hnames.name,        disks.hostinfo_idx,        disks.id,        disks.name,        disks.size,        disks.freespace order  hnames.name,           disks.id  

note query might different result! i'm focusing on problem, might wrong data row think holds max(hinfo.probetime).

or solve (and want):

select hinfo.idx,        hinfo.host_idx,        hinfo.processor_load,        hinfo.memory_total,        hinfo.memory_free,        hnames.idx,        hnames.name,        disks.hostinfo_idx,        disks.id,        disks.name,        disks.size,        disks.freespace,        hinfo.probetime   systeminfo.hostinfo hinfo        inner join systeminfo.hosts hnames                on hnames.idx = hinfo.host_idx        inner join systeminfo.disksinfo disks                on disks.hostinfo_idx = hinfo.idx  hinfo.probetime = (select max(probetime) systeminfo.hostinfo hi                                                 inner join systeminfo.hosts hn                                                 on hnames.idx = hinfo.host_idx                                                 inner join systeminfo.disksinfo d                                                 on disks.hostinfo_idx = hinfo.idx                           d.id = disks.id , hn.name = hnames.name) group  disks.id,           hnames.name order  hnames.name,           disks.id 

there's nice example in manual this: the rows holding group-wise maximum of column