So Seppe congratulations with your new slave Kenny! Sebastian Proost wrote:
It's ugly but it works (at least on this data and if I got the point of query in the first place)
SELECT a.locus_id, a.date, b.definition FROM function AS b INNER JOIN ( SELECT h.locus_id, max( h.modification_date ) AS `date`,max( f.modification_date ) AS `newdate` , definition FROM history AS h JOIN function AS f USING ( locus_id ) WHERE annotator_id =2 AND f.modification_date <= h.modification_date GROUP BY locus_id ) AS a WHERE b.modification_date = a.newdate
Kenny Billiau wrote:
You can win a free slave!
Kenny Billiau wrote:
Hi,
I've got a real problem (without a descent solution).
There are two tables, history and function, which both have their primary key on locus_id and modification_date. The table history has all versions, the table function can skip some dates. So if you want the data from function you will need the one with the modification_date which is the closest the modification_date of history.
I'm trying to get locus_id, the latest modification_date (both from history) and a definition (from function).
You can try on real data:
host: psbsql01 table: bogas_ectsi_tmp user: bogi pass: bogi
I had this, but the problem with this query is that we are selecting all rows with a modification_date <= histories modification_date. Afterwhich the group by doesn't really know what record to keep.
SELECT h.locus_id, max( h.modification_date ) AS `date` , definition FROM history AS h JOIN FUNCTION AS f USING ( locus_id ) WHERE annotator_id =2 AND f.modification_date <= h.modification_date GROUP BY locus_id ORDER BY `date` DESC LIMIT 0 , 30
A correct answer should be when definition has 'this is a valid definition' instead of 'n/a' for locus_id Esi000_0010
-Kenny
-- ================================================================== Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Evolutionary Genomics Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 fax:+32 (0)9 3313809 Website: http://bioinformatics.psb.ugent.be ==================================================================