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

Popular posts from this blog

apache - PHP Soap issue while content length is larger -

asynchronous - Python asyncio task got bad yield -

javascript - Complete OpenIDConnect auth when requesting via Ajax -