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

Take a ganders at SELECT DISTINCT

Answer 2

The SQL Query would look something like this:

SELECT DISTINCT (ID), TIME, DATA FROM TableName

Answer 3

This design is wrong in every way. It breaks the rules for first normal form. Unless those reasons were really compelling, I'd advise you to redesign this schema.

Answer 4

Add a real primary key to the table, maybe called RealId.

Then you can:

select *
from YourTable
where realid in (
    select min(realid)
    from YourTable
    group by id
)

Which would give you one row per id.

Answer 5

It sounds like you're looking for GROUP BY.

You can use a query similar to:

SELECT id, time, data GROUP BY ID

You'll definitely have to do some tweaking to get that query to work with the structure you have but that's the basic idea.

Tizag has a great reference for using GROUP BY with MySQL and PHP.

Answer 6

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.