How can I select only one row for each ID in MySQL?

Question

I have a MySQL table that holds many entries with repeated IDs (for various reasons) So you might have something like

ID TIME DATA
1  xx   xx
2  xx   xx
3  xx   xx
1  xx   xx
3  xx   xx

What query can I run through PHP to select each ID only once? So I would like my result set to look like

ID TIME DATA
1  xx   xx
2  xx   xx
3  xx   xx

Answer 1

The suggestion given by @karim79 and @Matthew Jones, that DISTINCT(ID) can fix this problem, is a common misconception about the way DISTINCT works in SQL. It doesn't help, because DISTINCT always applies to the whole row, not a single column. The parentheses are irrelevant, as they would be in the queries SELECT (1) versus SELECT 1.

The answer given by @T Pops is actually helpful in this case, because MySQL handles GROUP BY in a nonstandard way. See my answer to "Selecting a Column not in GROUP BY" for an explanation.

Another solution is to use LEFT OUTER JOIN creatively to query for the first row per ID. For instance, assuming that the TIME column is unique for a given ID, you could do the following:

SELECT t1.*
FROM MyTable t1 LEFT OUTER JOIN MyTable t2
  ON (t1.ID = t2.ID AND t1.TIME > t2.TIME)
WHERE t2.ID IS NULL;

If t1 is pointing to the earliest row in the group, then there can be no matching row in t2 with an earlier date. Therefore, the LEFT OUTER JOIN will find no match, and leave t2.* as NULL.