lets say I have this data id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5 And I want to select highest 3 scores: SELECT score FROM table ORDER BY score DESC LIMIT 0,3 result will be 9.5 9.3 9.1 but how can I select both the name and the score and I only want one score per person I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also is this possible in one query ? thanksuwel ! -- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
SELECT DISTINCT name, score FROM ..... djebus, even I know that !! 8-) On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ============================================================== 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 ============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true" - H. Simpson
if you want to make your query look more spectacular, you could go for a join with the same table of course. But Lieven's suggestion should just work, so i don't really see why you would do that, except maybe to impress the ladies :D On Fri, 02 Jul 2010 10:29:44 +0200, Lieven Sterck <lieven.sterck@psb.vib-ugent.be> wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
no ! this will only remove duplicates like dude1 9 dude1 9 if there's: dude1 9 dude1 9.5 dude2 9 I only want: dude1 9.5 dude2 9 Only 2 tries left
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;) SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3 Kenny On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
It's Marijn the 0th to the rescue! :D Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
not quite lieven, too many distincts and too little group by's in your query. see you all at the bbq On Fri, 02 Jul 2010 10:47:50 +0200, Lieven Sterck <lieven.sterck@psb.vib-ugent.be> wrote:
Eije ...
da haddek ook just doorgestuurd ... stoemen time-clock server thingie !!! >:o
On 02/07/2010 10:45, Sofie Van Landeghem wrote: It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3
this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de [1] Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de [2] http://www.goforsys.de [3] ======================================================================
-------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari [4]
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be [5]
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari [6]
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter. T.
On Fri, 02 Jul 2010 10:47:50 +0200, Lieven Sterck <lieven.sterck@psb.vib-ugent.be> wrote:
Eije ...
da haddek ook just doorgestuurd ... stoemen time-clock server thingie !!!>:o
On 02/07/2010 10:45, Sofie Van Landeghem wrote: It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3
this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ================================================================== 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 ==================================================================
Ask IT... Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
T.
On Fri, 02 Jul 2010 10:47:50 +0200, Lieven Sterck <lieven.sterck@psb.vib-ugent.be> wrote:
Eije ...
da haddek ook just doorgestuurd ... stoemen time-clock server thingie !!!>:o
On 02/07/2010 10:45, Sofie Van Landeghem wrote: It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
-- ================================================================== 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 ==================================================================
Thomas Van Parys wrote:
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
kan ook :P
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
Op 02-07-10 13:41, Frederik Delaere schreef:
Thomas Van Parys wrote:
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
kan ook :P
X-Sender: mavoo@mail.psb.ugent.be User-Agent: RoundCube Webmail/0.3.1 And what are you going to do about it? :-) T.
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
-- ================================================================== 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 ==================================================================
Thomas Van Parys wrote:
Op 02-07-10 13:41, Frederik Delaere schreef:
Thomas Van Parys wrote:
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
kan ook :P
X-Sender: mavoo@mail.psb.ugent.be User-Agent: RoundCube Webmail/0.3.1
And what are you going to do about it? :-)
T.
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
(test) On Fri, 02 Jul 2010 13:41:49 +0200, Thomas Van Parys <thpar@psb.vib-ugent.be> wrote:
Op 02-07-10 13:41, Frederik Delaere schreef:
Thomas Van Parys wrote:
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
kan ook :P
X-Sender: mavoo@mail.psb.ugent.be User-Agent: RoundCube Webmail/0.3.1
And what are you going to do about it? :-)
T.
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
Op 02-07-10 13:44, Frederik Delaere schreef:
(test)
Tiens... die klopt ook. Ja, Marijn zal nu wel onderweg zijn... vanavond nog eens testen. T.
On Fri, 02 Jul 2010 13:41:49 +0200, Thomas Van Parys <thpar@psb.vib-ugent.be> wrote:
Op 02-07-10 13:41, Frederik Delaere schreef:
Thomas Van Parys wrote:
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
kan ook :P
X-Sender: mavoo@mail.psb.ugent.be User-Agent: RoundCube Webmail/0.3.1
And what are you going to do about it? :-)
T.
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
> not quite lieven, too many distincts and too little group by's in > your > query. > > see you all at the bbq > > Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
-- ================================================================== 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 ==================================================================
Thomas Van Parys wrote:
Op 02-07-10 13:39, Thomas Abeel schreef:
Ask IT...
webmail settings?
was idd iets in de webmail config is gefixt nu (ik had dat blijkbaar voor mijn account al zelf goed gezet, maar herinnerde mij dat niet meer)
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
Thomas Abeel wrote:
Ask IT...
IT told him to wait with the ubuntu 10.04 update .. :) its the root of all his troubles !
Thomas Van Parys wrote:
Op 02-07-10 10:47, mavoo schreef:
not quite lieven, too many distincts and too little group by's in your query.
see you all at the bbq
Why are you using such a weird psb email address in your FROM-field? "mavoo@mail.psb.ugent.be" It annoys the mailman mod filter.
T.
On Fri, 02 Jul 2010 10:47:50 +0200, Lieven Sterck <lieven.sterck@psb.vib-ugent.be> wrote:
Eije ...
da haddek ook just doorgestuurd ... stoemen time-clock server thingie !!!>:o
On 02/07/2010 10:45, Sofie Van Landeghem wrote: It's Marijn the 0th
to
the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
Hey .. o_O .. what ? But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!) Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue? On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
Everything is *always* a priority queue! Kenny Billiau wrote:
Hey .. o_O .. what ?
But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!)
Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue?
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
Then we might as well add Bram too, no? We can ask - but we might want to warn them about the huge amount of spam created here. By me, mainly. Cheers! Lieven Sterck wrote:
btw ...
It might not be a bad idea to inlcude our new-comer (CESIM) in this group ;-) especially for the scientific part that is ;-) ....
On 02/07/2010 10:51, Sofie Van Landeghem wrote:
Everything is *always* a priority queue!
Kenny Billiau wrote:
Hey .. o_O .. what ?
But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!)
Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue?
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ============================================================== 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
============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true" - H. Simpson
------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
The problem with a group by is that is creates a temporary table. So unless this tmp-table is created fully from a covering index (= retrieval of this index results in the retrieval of all requested information in the SELECT clause), this will be ass slow. It's vaguely explained here: http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html But as long as you don't go into the 1M row range, you won't notice anything from this ;) On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
Everything is *always* a priority queue!
Kenny Billiau wrote: Hey .. o_O .. what ?
But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!)
Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue?
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
this might actually explain some of my performance issues :-/ So the solution is: put an index on all fields of all tables, to be on the safe side :-) Kenny Billiau wrote:
The problem with a group by is that is creates a temporary table. So unless this tmp-table is created fully from a covering index (= retrieval of this index results in the retrieval of all requested information in the SELECT clause), this will be ass slow.
It's vaguely explained here: http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
But as long as you don't go into the 1M row range, you won't notice anything from this ;)
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
Everything is *always* a priority queue!
Kenny Billiau wrote: Hey .. o_O .. what ?
But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!)
Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue?
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de>
Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== 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 http://bioinformatics.psb.ugent.be ==================================================================
If you do that, you double the amount of data that has to be stored in memory ;) Anyway. The rule of thumb is to have 'left-most-indexes', which usually means, you can group indexes together, as long as you specify the fields in SELECT and WHERE in the order they appear in the index. You can leave out the fields that are right most and still have the advantage of your index for the left-most fields. Also, don't forget to set the amount of memory used to actually store the index in memory is set high enough in my.cnf. for innoDB you can set innodb_buffer_pool_size can be more than half of your available RAM. But I guess for your shared server setup, this will only give you a vague improvement, unless PLAZA really is the main user of the server. Anyway :) On Fri, 2 Jul 2010, Michiel Van Bel wrote:
this might actually explain some of my performance issues :-/ So the solution is: put an index on all fields of all tables, to be on the safe side :-)
Kenny Billiau wrote:
The problem with a group by is that is creates a temporary table. So unless this tmp-table is created fully from a covering index (= retrieval of this index results in the retrieval of all requested information in the SELECT clause), this will be ass slow.
It's vaguely explained here: http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
But as long as you don't go into the 1M row range, you won't notice anything from this ;)
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
Everything is *always* a priority queue!
Kenny Billiau wrote: Hey .. o_O .. what ?
But that would make Francis Marijn 'the buffer underflow'? (wait, Marijn[-1] in perl would just give the last element .. which is Marijn .. so Marijn = Francis, which makes me Francis the second, which actualy makes sense!)
Should I now also explain the drawback of loosing the ability of using indexes with group by for extra points ? Or ain't this a priority queue?
On Fri, 2 Jul 2010, Sofie Van Landeghem wrote:
It's Marijn the 0th to the rescue! :D
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de><mailto:billiau@mpimp-golm.mpg.de>
Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- Sofie Van Landeghem PhD Student VIB Department of Plant Systems Biology, Ghent University Bioinformatics and Systems Biology Technologiepark 927, 9052 Gent, BELGIUM Tel: +32 (0)9 331 36 95 Website: http://bioinformatics.psb.ugent.be
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
doesn't give correct results :( Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
Ow that sucks :/ .. but what is wrong? On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
frlae hier bij mij .... beeke vanalles hij 'skipt' bvb name in de result On 02/07/2010 11:06, Kenny Billiau wrote:
Ow that sucks :/
.. but what is wrong?
On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ============================================================== 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 ============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true" - H. Simpson
My two cents SELECT * FROM (SELECT name, max(score) as m_score FROM table GROUP BY name ) AS a ORDER BY m_score DESC LIMIT 0,3 Kenny Billiau schreef:
Ow that sucks :/
.. but what is wrong?
On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
SOLVED IT !!! 'order by max(score)' does the trick! 8-) On 02/07/2010 11:12, Sebastian Proost wrote:
My two cents
SELECT * FROM (SELECT name, max(score) as m_score FROM table GROUP BY name ) AS a ORDER BY m_score DESC LIMIT 0,3
Kenny Billiau schreef:
Ow that sucks :/
.. but what is wrong?
On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ============================================================== 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 ============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true" - H. Simpson
And btw, limit 0,3 is a weird way of writing limit 3 ;) On Fri, 2 Jul 2010, Lieven Sterck wrote:
SOLVED IT !!!
'order by max(score)' does the trick!
8-)
On 02/07/2010 11:12, Sebastian Proost wrote: My two cents
SELECT * FROM (SELECT name, max(score) as m_score FROM table GROUP BY name ) AS a ORDER BY m_score DESC LIMIT 0,3
Kenny Billiau schreef: Ow that sucks :/
.. but what is wrong?
On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote: If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de<mailto:billiau@mpimp-golm.mpg.de> Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
________________________________
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be<mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ PLAZA, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ============================================================== 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<mailto:lieven.sterck@psb.vib-ugent.be> Website: http://bioinformatics.psb.ugent.be
============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true" - H. Simpson
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ======================================================================
lieven you am the best ! (for now) Lieven Sterck wrote:
SOLVED IT !!!
'order by max(score)' does the trick!
8-)
On 02/07/2010 11:12, Sebastian Proost wrote:
My two cents
SELECT * FROM (SELECT name, max(score) as m_score FROM table GROUP BY name ) AS a ORDER BY m_score DESC LIMIT 0,3
Kenny Billiau schreef:
Ow that sucks :/
.. but what is wrong?
On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote: > lets say I have this data > > id name score > 1 dude1 9.1 > 2 dude2 9.5 > 3 dudette1 9.0 > 4 dude1 9.3 > 5 dudette1 8.5 > > And I want to select highest 3 scores: > > SELECT score FROM table ORDER BY score DESC LIMIT 0,3 > > result will be > > 9.5 > 9.3 > 9.1 > > but how can I select both the name and the score and I only want > one > score per person > > I can do: SELECT DISTINCT name FROM table ORDER BY score DESC > LIMIT 0,3 > this will give me the correct names in the correct order, but I > want > their score also > > is this possible in one query ? > > > thanksuwel !
-- ====================================================================== Kenny Billiau Bioinformatics Group / GoFORSYS Scientific Programmer +49 331 567 8626 billiau@mpimp-golm.mpg.de Max Planck Institute for Molecular Plant Physiology Am Mühlenberg 1, 14476 Potsdam-Golm, Germany http://bioinformatics.mpimp-golm.mpg.de http://www.goforsys.de ====================================================================== ------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Sebastian Proost, PhD Student
Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Bioinformatics and Systems Biology division Ghent University Technologiepark 927, 9052 Gent, BELGIUM
email: sebastian.proost@psb.vib-ugent.be <mailto:sebastian.proost@psb.vib-ugent.be> website: http://bioinformatics.psb.ugent.be/ ------------------------------------------------------------------ *PLAZA*, a resource for Plant Comparative Genomics http://bioinformatics.psb.ugent.be/plaza/ ==================================================================
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ============================================================== 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
============================================================== "Facts are meaningless. You could use facts to prove anything that's even remotely true" - H. Simpson
------------------------------------------------------------------------
_______________________________________________ Binari Implicitly Neglects All Recursive Iterations https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Frederik Delaere Tel:+32 (0)9 331 38 14 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM frlae@psb.vib-ugent.be http://www.psb.vib-ugent.be ==================================================================
Op 02-07-10 11:12, Sebastian Proost schreef:
My two cents
SELECT * FROM (SELECT name, max(score) as m_score FROM table GROUP BY name ) AS a ORDER BY m_score DESC LIMIT 0,3
Just read this discussion. Isn't this problem very similar to one of Kenny's exam questions of last year? T.
Kenny Billiau schreef:
Ow that sucks :/
.. but what is wrong?
On Fri, 2 Jul 2010, Frederik Delaere wrote:
doesn't give correct results :(
Kenny Billiau wrote:
If you want the unique rows, use distinct. If you want the highest score per person, you'll have to use group by with an aggregate function ;)
SELECT name, max(score) FROM table GROUP BY name ORDER BY score DESC LIMIT 0,3
Kenny
On Fri, 2 Jul 2010, Lieven Sterck wrote:
SELECT DISTINCT name, score FROM .....
djebus, even I know that !! 8-)
On 02/07/2010 10:23, Frederik Delaere wrote:
lets say I have this data
id name score 1 dude1 9.1 2 dude2 9.5 3 dudette1 9.0 4 dude1 9.3 5 dudette1 8.5
And I want to select highest 3 scores:
SELECT score FROM table ORDER BY score DESC LIMIT 0,3
result will be
9.5 9.3 9.1
but how can I select both the name and the score and I only want one score per person
I can do: SELECT DISTINCT name FROM table ORDER BY score DESC LIMIT 0,3 this will give me the correct names in the correct order, but I want their score also
is this possible in one query ?
thanksuwel !
-- ================================================================== 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 ==================================================================
participants (9)
-
Frederik Delaere -
Kenny Billiau -
Lieven Sterck -
mavoo -
Michiel Van Bel -
Sebastian Proost -
Sofie Van Landeghem -
Thomas Abeel -
Thomas Van Parys