Avoid selecting duplicate MySQL rows

Question

I am selecting rows with

SELECT `playerid` FROM `lb-world` WHERE replaced=50 AND type=0 >= 0.01

But in my table I have

playerid | replaced | type | date
12       | 50       | 0    | 2010
12       | 50       | 0    | 2011
12       | 50       | 0    | 2012

How to select it just once?

Marco · Accepted Answer · 2012-08-01 12:54:48Z

1

Try this:

SELECT `playerid` FROM `lb-world` 
WHERE replaced=50 AND type=0
GROUP BY playerid, replaced, type

This should be generic.
Anyway, if you're sure you just want playerid, use this

SELECT DISTINCT playerid FROM lb-world
WHERE replaced=50 AND type=0

answered Aug 1, 2012 at 12:54

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Why GROUP BY? There's only playerid in the select list

@Michael: yes, you're right... but if he wants to add some field it's still working... anyway, ok I will change my answer

I think using GROUP BY is perfectly valid as well. As stated in MySQL documentation, in term of optimization, both functions are very close. dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Tchoupi · Accepted Answer · 2012-08-01 12:55:08Z

0

If you only want playerid, you can use DISTINCT

SELECT DISTINCT `playerid` FROM `lb-world` WHERE replaced=50 AND type=0 >= 0.01

answered Aug 1, 2012 at 12:55

Tchoupi

14.7k5 gold badges39 silver badges71 bronze badges

IlikeITalot · Accepted Answer · 2012-08-01 12:55:32Z

0

SELECT DISTINCT playerid..

as @Michael said

answered Aug 1, 2012 at 12:55

IlikeITalot

512 silver badges5 bronze badges