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 ==================================================================