sql - How to get the distinct of multiple fields in MySQL? -
i know there're several existing questions one, mine little bit complicated (for me).
for example, have index, data, log , flag these 4 tables.
index: id program compiler flag_id data_id data: id machine runtime date index_id log: index_id data_id log flag: id flag_name
the flag_id, data_id , index_id indicate corresponding table's main id. main id auto incremental , unique in index , data tables. flag table's id duplicated. example, program may use different flags compile:
index: 123 jacobi gcc 11 345 data: 345 host1 3:21 2015-05-13 22:56:12 123 log: 123 345 "pass" flag: 11 "-g" 11 "-mp" 11 "-static"
so program compiled like:
gcc -g -mp -static jacobi.c
and execution time 3'21'' , finished 2015-05-13 22:56:12.
the thing is, many people may run program , may due various of reasons, exact same test may pass or fail. , last run (based on timestamp--date or max(data_id) or max(index_id) same test) showed in webpage. how determine whether tests same (distinct)? these field should same:
program, compiler , flags (no order issue)
so, see, flags stored in flag table. don't know how correlate index , flag table find same test.
final example show expected output:
index: 123 jacobi gcc 11 345 data: 345 host1 3:21 2015-05-13 22:56:12 123 log: 123 345 "pass" flag: 11 "-g" 11 "-mp" 11 "-static" index: 126 jacobi gcc 15 397 data: 397 host2 3:14 2015-05-13 23:13:28 126 log: 126 397 "fail" flag: 15 "-g" 15 "-mp" 15 "-static"
here, these 2 records, same test since they're using same compiler compile same program same flags. second 1 latest one. because can tell either index_id, data_id or finishing date.
if there're 100000 records in database , maybe 30000 of them same tests, how these 30000 tests with last run result, pass or fail? like:
gcc -g -mp -static jacobi.c fail gcc -g -mp jacobi.c pass gcc -o3 mm.c pass ...
i know it's wordy, patience , appreciated!
i appear attracted self-flagellation. anyway, first thing first - assembling flags string, , getting results.:
select i.compiler, group_concat(f.flag_name order f.flag_name asc separator ' ') flags, i.program, d.date, l.log `index` inner join `flag` f on i.flag_id = f.id inner join `data` d on i.data_id = d.id inner join `log` l on i.id = l.index_id group i.id
the order by
in group_concat
ensures same set of flags appear in same order.
this should give result looks compiler | flag1 flag 2 ... | program | date
next step group compiler, flags , program, , max(date)
. querying results of first query.
select compiler, flags, program, max(`date`) max_date ( select i.compiler, group_concat(f.flag_name order f.flag_name asc separator ' ') flags, i.program, d.date `index` inner join `flag` f on i.flag_id = f.id inner join `data` d on i.data_id = d.id group i.id ) q group compiler, flags, program;
we know recent run time each combo of compiler, flags , program.
we need join our first query one, find row max date, , associated data:
select q1.* ( select i.compiler, group_concat(f.flag_name order f.flag_name asc separator ' ') flags, i.program, d.date, l.log `index` inner join `flag` f on i.flag_id = f.id inner join `data` d on i.data_id = d.id inner join `log` l on i.id = l.index_id group i.id ) q1 inner join ( select compiler, flags, program, max(`date`) max_date ( select i.compiler, group_concat(f.flag_name order f.flag_name asc separator ' ') flags, i.program, d.date `index` inner join `flag` f on i.flag_id = f.id inner join `data` d on i.data_id = d.id group i.id ) q group compiler, flags, program ) q2 on q1.compiler = q2.compiler , q1.flags = q2.flags , q1.program = q2.program , q1.`date` = q2.max_date
sql fiddle broken right can't test - should pretty close.
Comments
Post a Comment