GROUP_CONCAT for latest date

[gin]

MySQL: 5.1.53

 

I have a table:

+------------+---------+----------+
| date       | id      | category |
+------------+---------+----------+
| 0000-00-00 | lyn     | 3ds      |
| 0000-00-00 | moonc   | flms     |
| 0000-00-00 | moonc   | vfxd     |
| 2011-09-02 | buddy   | 2ds      |
| 2011-09-02 | buddy   | 3dj      |
| 2011-09-01 | buddy   | dess     |
+------------+---------+----------+

 

I want the latest categories for each id. The thing is, there may be more than 1 category, so I used a GROUP_CONCAT. Unfortunately, this gives me all the categories since time immemorial. Observe:

SELECT id, MAX(date) as date , GROUP_CONCAT(category) FROM staff_acccat GROUP BY id;

+---------+------------+------------------------+
| id      | date       | GROUP_CONCAT(category) |
+---------+------------+------------------------+
| buddy   | 2011-09-02 | 2ds,3dj,dess           |
| lyn     | 0000-00-00 | 3ds                    |
| moonc   | 0000-00-00 | flms,vfxd              |
+---------+------------+------------------------+

 

What I actually want:

+---------+------------+------------------------+
| id      | date       | GROUP_CONCAT(category) |
+---------+------------+------------------------+
| buddy   | 2011-09-02 | 2ds,3dj                |     <-- 1 less item
| lyn     | 0000-00-00 | 3ds                    |
| moonc   | 0000-00-00 | flms,vfxd              |
+---------+------------+------------------------+

 

Any advice much appreciated!

[fenway]

That's not a job for GROUP BY.

 

Join the table to itself, on (id), and use t1.date > t2.date in the ON clause.

 

Simple as that.

[gin]

Sorry, I'm just not getting it. This is the closest I've gotten:

SELECT a.date, a.id, GROUP_CONCAT(a.category) FROM staff_acccat a
INNER JOIN staff_acccat b ON a.date > b.date AND a.id = b.id
GROUP BY id;
+------------+-------+--------------------------+
| date       | id    | GROUP_CONCAT(a.category) |
+------------+-------+--------------------------+
| 2011-09-02 | buddy | 2ds,3dj                  |
+------------+-------+--------------------------+

 

At least I got rid of the old data, but data with no updates are all gone, so not quite there yet. Unless the idea is to then join the two sets of data somehow?

 

Could you elaborate a bit lot more? 

[fenway]

Well, a LEFT JOIN will fix the "no updates" problem -- and just drop the group_concat all together.

[gin]

Um, sorry, I seem to have twisted myself all the way back to the start. I still don't know how to filter out the old data.

SELECT a.date, a.id, a.category FROM staff_acccat a
LEFT JOIN staff_acccat b ON a.date > b.date AND a.id = b.id

+------------+-------+----------+
| date       | id    | category |
+------------+-------+----------+
| 0000-00-00 | lyn   | 3ds      |
| 0000-00-00 | moonc | flms     |
| 0000-00-00 | moonc | vfxd     |
| 2011-09-02 | buddy | 2ds      |
| 2011-09-02 | buddy | 3dj      |
| 2011-09-01 | buddy | dess     |
+------------+-------+----------+

[fenway]

You'll need to add a where clause to select either (a) those without updates (i.e. b.id IS NULL) or (b) those with updates (a.date > b.date), not both.

 

Alternatively, you could UNION these two result sets -- might be easier to read, and definitely faster for the optimizer.

[fenway]

Ignore my previous response -- I wasn't thinking straight.

 

See the answer I just posted here - http://www.phpfreaks.com/forums/index.php?topic=343277.0.

 

So:

SELECT a.date, a.id, a.category 
FROM staff_acccat a
LEFT JOIN staff_acccat b ON ( a.date < b.date AND a.id = b.id )
WHERE b.date IS NULL

 

Find all records for where there is no "more recent" match (on ID), looking at date.

[gin]

Thank you! Such a slight change; I spent days cracking my head and getting no where.