views:

3

answers:

0

I have a table that stores "comments" on "parent" items in various "languages", with the "date" (dateTime) it was added. Example data:

DROP TABLE IF EXISTS comments;
CREATE TABLE comments(parentid INT, language char(2), body TEXT, date DATETIME);
INSERT INTO comments VALUES
(1, 'en', 'english comment 1-1', '2010-09-01 11:00:00'),
(1, 'en', 'english comment 1-2', '2010-09-02 10:00:00'),
(1, 'fr', 'french  comment 1-1', '2010-09-01 12:00:00'),
(1, 'it', 'italian comment 1-1', '2010-09-02 11:00:00'),
(2, 'fr', 'french  comment 2-1', '2010-09-02 10:00:00'),
(2, 'en', 'english comment 2-1', '2010-09-01 11:00:00'),
(2, 'it', 'italian comment 2-1', '2010-09-01 10:00:00'),
(2, 'it', 'italian comment 2-2', '2010-09-01 12:00:00')

I would like a query that would show something like the following, i.e. one that would give a single line per unique parentid, one column for each language, and only the most recent comments for each language.

+----------+---------------------+---------------------+---------------------+
| parentid | en                  | fr                  | it                  |
+----------+---------------------+---------------------+---------------------+
|        1 | english comment 1-2 | french  comment 1-1 | italian comment 1-1 |
|        2 | english comment 2-1 | french  comment 2-1 | italian comment 2-2 |
+----------+---------------------+---------------------+---------------------+

The closest I've come so far is the following, taken from here:

SELECT 
  parentid, 
  GROUP_CONCAT(if(language = 'en', body, NULL)) AS 'en',
  GROUP_CONCAT(if(language = 'fr', body, NULL)) AS 'fr',
  GROUP_CONCAT(if(language = 'it', body, NULL)) AS 'it'
FROM comments
GROUP BY parentid;

I don't want the concatenation; I only want the newest of the comments to appear. But without the GROUP_CONCAT, I get blanks like this:

+----------+-------------------+-------------------+------+
| parentid | en                | fr                | it   |
+----------+-------------------+-------------------+------+
|        1 | english comment 1 | NULL              | NULL |
|        2 | NULL              | french  comment 1 | NULL |
+----------+-------------------+-------------------+------+

I'm sure I need a max(date) in there somewhere, but I'm not sure how to incorporate it. Any advice would be appreciated.