mysql> select mn.journal_id, mn.impact_factor, mn.year, `yearcount`.`lastyear` from impact_factors as mn join (select i.journal_id as jjid, max(i.year) as `lastyear` from impact_factors i group by i.journal_id) as `yearcount` on (mn.journal_id = `yearcount`.jjid) where mn.journal_id in (92, 35, 29, 97); +------------+---------------+------+----------------------+ | journal_id | impact_factor | year | lastyear | +------------+---------------+------+----------------------+ | 29 | 7.603 | 2005 | 0008 | | 29 | 7.883 | 2002 | 0008 | | 29 | 7.764 | 2006 | 0008 | | 29 | 8.624 | 2001 | 0008 | | 29 | 7.663 | 2003 | 0008 | | 29 | 10.088 | 1999 | 0008 8 | etc... | 92 | 11.088 | 2005 | 0008 8 | | 92 | 11.295 | 2004 | 0008 5 | | 92 | 10.751 | 2002 | 0008 1 | | 92 | 9.868 | 2006 | 0008 | | 92 | 11.081 | 2001 | 0008 1 | etc... Thanks to Sofie for noticing a corelation between impact_factor and lastyear. Anyone knows how to get me a '2008' in lastyear? T. -- ================================================================== Thomas Van Parys 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 thomas.vanparys@psb.vib-ugent.be http://bioinformatics.psb.ugent.be ==================================================================
De correlatie zijnde dat "lastyear" de laatste digit bevat van impact_factor als die langer dan 4 digits is! Thomas Van Parys wrote:
mysql> select mn.journal_id, mn.impact_factor, mn.year, `yearcount`.`lastyear` from impact_factors as mn join (select i.journal_id as jjid, max(i.year) as `lastyear` from impact_factors i group by i.journal_id) as `yearcount` on (mn.journal_id = `yearcount`.jjid) where mn.journal_id in (92, 35, 29, 97); +------------+---------------+------+----------------------+ | journal_id | impact_factor | year | lastyear | +------------+---------------+------+----------------------+ | 29 | 7.603 | 2005 | 0008 | | 29 | 7.883 | 2002 | 0008 | | 29 | 7.764 | 2006 | 0008 | | 29 | 8.624 | 2001 | 0008 | | 29 | 7.663 | 2003 | 0008 | | 29 | 10.088 | 1999 | 0008 8 |
etc...
| 92 | 11.088 | 2005 | 0008 8 | | 92 | 11.295 | 2004 | 0008 5 | | 92 | 10.751 | 2002 | 0008 1 | | 92 | 9.868 | 2006 | 0008 | | 92 | 11.081 | 2001 | 0008 1 |
etc...
Thanks to Sofie for noticing a corelation between impact_factor and lastyear.
Anyone knows how to get me a '2008' in lastyear?
T.
-- 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
Wat moet er eigenlijk in de kolom "lastyear" zitten? Want dat vind ik nogal een vreemd iets :-/ Sofie Van Landeghem wrote:
De correlatie zijnde dat "lastyear" de laatste digit bevat van impact_factor als die langer dan 4 digits is!
Thomas Van Parys wrote:
mysql> select mn.journal_id, mn.impact_factor, mn.year, `yearcount`.`lastyear` from impact_factors as mn join (select i.journal_id as jjid, max(i.year) as `lastyear` from impact_factors i group by i.journal_id) as `yearcount` on (mn.journal_id = `yearcount`.jjid) where mn.journal_id in (92, 35, 29, 97); +------------+---------------+------+----------------------+ | journal_id | impact_factor | year | lastyear | +------------+---------------+------+----------------------+ | 29 | 7.603 | 2005 | 0008 | | 29 | 7.883 | 2002 | 0008 | | 29 | 7.764 | 2006 | 0008 | | 29 | 8.624 | 2001 | 0008 | | 29 | 7.663 | 2003 | 0008 | | 29 | 10.088 | 1999 | 0008 8 |
etc...
| 92 | 11.088 | 2005 | 0008 8 | | 92 | 11.295 | 2004 | 0008 5 | | 92 | 10.751 | 2002 | 0008 1 | | 92 | 9.868 | 2006 | 0008 | | 92 | 11.081 | 2001 | 0008 1 |
etc...
Thanks to Sofie for noticing a corelation between impact_factor and lastyear.
Anyone knows how to get me a '2008' in lastyear?
T.
-- ================================================================== Michiel Van Bel PhD student 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 mibel@psb.vib-ugent.be http://www.psb.vib-ugent.be ================================================================== We are all in the gutter, but some of us are looking at the stars.
Op 29-03-10 14:22, Michiel Van Bel schreef:
Wat moet er eigenlijk in de kolom "lastyear" zitten? Want dat vind ik nogal een vreemd iets :-/
Ok... misschien zijn er simpeler methoden, maar ik kom er nu niet op. Ik zoek voor elke journal_id het laatste jaar (lastyear) waarvoor er een impact_factor bestaat. Dat laatste van journal_id/lastyear join ik dan aan m'n lijst journal_id, impact_factor. Uiteindelijke doel: voor elk journal de meest recente IF weergeven.
Sofie Van Landeghem wrote:
De correlatie zijnde dat "lastyear" de laatste digit bevat van impact_factor als die langer dan 4 digits is!
Thomas Van Parys wrote:
mysql> select mn.journal_id, mn.impact_factor, mn.year, `yearcount`.`lastyear` from impact_factors as mn join (select i.journal_id as jjid, max(i.year) as `lastyear` from impact_factors i group by i.journal_id) as `yearcount` on (mn.journal_id = `yearcount`.jjid) where mn.journal_id in (92, 35, 29, 97); +------------+---------------+------+----------------------+ | journal_id | impact_factor | year | lastyear | +------------+---------------+------+----------------------+ | 29 | 7.603 | 2005 | 0008 | | 29 | 7.883 | 2002 | 0008 | | 29 | 7.764 | 2006 | 0008 | | 29 | 8.624 | 2001 | 0008 | | 29 | 7.663 | 2003 | 0008 | | 29 | 10.088 | 1999 | 0008 8 |
etc...
| 92 | 11.088 | 2005 | 0008 8 | | 92 | 11.295 | 2004 | 0008 5 | | 92 | 10.751 | 2002 | 0008 1 | | 92 | 9.868 | 2006 | 0008 | | 92 | 11.081 | 2001 | 0008 1 |
etc...
Thanks to Sofie for noticing a corelation between impact_factor and lastyear.
Anyone knows how to get me a '2008' in lastyear?
T.
-- ================================================================== Thomas Van Parys 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 thomas.vanparys@psb.vib-ugent.be http://bioinformatics.psb.ugent.be ==================================================================
Op 29-03-10 14:35, Sebastian Proost schreef:
Thomas Van Parys schreef:
Op 29-03-10 14:22, Michiel Van Bel schreef:
Wat moet er eigenlijk in de kolom "lastyear" zitten? Want dat vind ik nogal een vreemd iets :-/
Ok... misschien zijn er simpeler methoden, maar ik kom er nu niet op.
Ik zoek voor elke journal_id het laatste jaar (lastyear) waarvoor er een impact_factor bestaat. Dat laatste van journal_id/lastyear join ik dan aan m'n lijst journal_id, impact_factor. Uiteindelijke doel: voor elk journal de meest recente IF weergeven.
Wat geeft deze subquery als resultaat ?
select i.journal_id as jjid, max(i.year) as `lastyear` from impact_factors i group by i.journal_id
Mja, had ik ook al geprobeerd. Een nette lijst van journal_ids | lastyear: +------+----------+ | jjid | lastyear | +------+----------+ | 3 | 2008 | | 5 | 2008 | | 6 | 2008 | | 7 | 2008 | | 8 | 2008 | | 9 | 2008 | | 11 | 2008 | | 12 | 2008 | | 13 | 2008 | | 14 | 2008 | | 15 | 2006 | | 16 | 2008 | | 17 | 2008 | -- ================================================================== Thomas Van Parys 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 thomas.vanparys@psb.vib-ugent.be http://bioinformatics.psb.ugent.be ==================================================================
WTF??? :-[ Sofie Van Landeghem wrote:
De correlatie zijnde dat "lastyear" de laatste digit bevat van impact_factor als die langer dan 4 digits is!
Thomas Van Parys wrote:
mysql> select mn.journal_id, mn.impact_factor, mn.year, `yearcount`.`lastyear` from impact_factors as mn join (select i.journal_id as jjid, max(i.year) as `lastyear` from impact_factors i group by i.journal_id) as `yearcount` on (mn.journal_id = `yearcount`.jjid) where mn.journal_id in (92, 35, 29, 97); +------------+---------------+------+----------------------+ | journal_id | impact_factor | year | lastyear | +------------+---------------+------+----------------------+ | 29 | 7.603 | 2005 | 0008 | | 29 | 7.883 | 2002 | 0008 | | 29 | 7.764 | 2006 | 0008 | | 29 | 8.624 | 2001 | 0008 | | 29 | 7.663 | 2003 | 0008 | | 29 | 10.088 | 1999 | 0008 8 |
etc...
| 92 | 11.088 | 2005 | 0008 8 | | 92 | 11.295 | 2004 | 0008 5 | | 92 | 10.751 | 2002 | 0008 1 | | 92 | 9.868 | 2006 | 0008 | | 92 | 11.081 | 2001 | 0008 1 |
etc...
Thanks to Sofie for noticing a corelation between impact_factor and lastyear.
Anyone knows how to get me a '2008' in lastyear?
T.
-- ============================================================== Lieven Sterck, PhD Tel:+32 (0)9 3313821 Fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, UGent Bioinformatics and Evolutionary Genomics Division Technologiepark 927, B-9052 Gent, Belgium Email: lieven.sterck@psb.vib-ugent.be Website: http://bioinformatics.psb.ugent.be ============================================================== "You tried your best and you failed miserably. The lesson is: never try!" - H. Simpson
participants (5)
-
Lieven Sterck -
Michiel Van Bel -
Sebastian Proost -
Sofie Van Landeghem -
Thomas Van Parys