CODEWITHSUNDEEP
phpsqlmysql
Rudebuyrock
Rudebuyrock

Reputation:

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

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

Upvotes: 4

Views: 26889

Answers (6)

user3668456
user3668456

Reputation: 151

SELECT * FROM MyTable GROUP BY ID ORDER BY ID ASC

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562398

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.

Upvotes: 14

Travis
Travis

Reputation: 12379

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.

Upvotes: 6

Andomar
Andomar

Reputation: 238116

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.

Upvotes: 0

Matthew Jones
Matthew Jones

Reputation: 26190

The SQL Query would look something like this:

SELECT DISTINCT (ID), TIME, DATA FROM TableName

Upvotes: -1

Aiden Bell
Aiden Bell

Reputation: 28386

Take a ganders at SELECT DISTINCT

Upvotes: 5

Related Questions