Fetch the newest row grouped by a column in MySQL
Create a Subquery that Gathers Keys from the the log_table
with Maximum Date Per Entity. Then, perform an INNER JOIN of that Subquery back to the log_table
.
SELECT
B.entity_id,B.last_log_date,B.last_comment
FROM
(
SELECT entity_id,MAX(last_log_date) last_log_date
FROM log_table GROUP BY entity_id
) A INNER JOIN B USING (entity_id,last_log_date);
Give it a Try !!!
You can really speed this up if you have a compound index like this
ALTER TABLE log_table ADD INDEX entity_date_ndx (entity_id,last_log_date);
Indexes on each separate column may yield index merges. This compound index will bypass that.
Please try LEFT JOIN
instead of INNER JOIN
SELECT
B.entity_id,B.last_log_date,B.last_comment
FROM
(
SELECT entity_id,MAX(last_log_date) last_log_date
FROM log_table GROUP BY entity_id
) A LEFT JOIN B USING (entity_id,last_log_date);
The subquery works; here's how you would do it without a subquery:
SELECT
`entity_id`,
SUBSTRING_INDEX(GROUP_CONCAT(`date` ORDER BY `date` DESC), ',', 1) AS last_log_date,
SUBSTRING_INDEX(GROUP_CONCAT(`comment` ORDER BY `date` DESC), ',', 1) AS last_comment
FROM `log_table`
GROUP BY `entity_id`
The query above uses GROUP_CONCAT
to generate a long concatenation of values per group, which is then parsed to extract first token via SUBSTRING_INDEX
.
You could have an excellent way of solving it if only MySQL supported Window Functions (aka Analytic Functions). It does, not , and we are left with hacks around GROUP_CONCAT
.