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 ==================================================================
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 ==================================================================
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
-- ================================================================== Sebastian Proost PhD Student Tel:+32 (0)9 331 36 92 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM sepro@psb.ugent.be http://www.psb.ugent.be ==================================================================
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 ==================================================================
t t t .. as this question was one for which Lieven acupunctured his TFT screen, I presume Lieven will be the slave .. Eventhough my preference goes to one of the recently added binari guys (this is you stmei, nithu, wablo, frpuy!) Sofie Van Landeghem wrote:
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
-- ================================================================== 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 ==================================================================
seppe, Lieven has agreed! Kenny Billiau wrote:
t t t .. as this question was one for which Lieven acupunctured his TFT screen, I presume Lieven will be the slave ..
Eventhough my preference goes to one of the recently added binari guys (this is you stmei, nithu, wablo, frpuy!)
Sofie Van Landeghem wrote:
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
-- ================================================================== 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 ==================================================================
participants (3)
-
Kenny Billiau -
Sebastian Proost -
Sofie Van Landeghem