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.