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
-- ================================================================== Kenny Billiau Web Developer Tel:+32 (0)9 331 36 95 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM kenny.billiau@psb.ugent.be http://bioinformatics.psb.ugent.be ==================================================================