MYSQL join with sort and group by choosing the element to display

Question

I have 2 tables, evt and content. content is link to evt throw the column content_evt_fk (to make it simpler, you can replace evt by article and content by comment for a blog database).

What I'm trying to do is to have in one query, the evt id, the evt name, the number of content related to it, and the id and the text of the last inserted content related to this evt.

here is the code :

    SELECT 
    `evt`.`evt_id`,
    `evt`.`name`,
    `content`.`content_id`,
    `content`.`content_text`,
    count(*) as `evt_cont`
FROM
    `t_evt` as `evt`,
    `t_content` AS `content`
WHERE
    `evt`.`evt_id` = `content`.`content_evt_fk`
group by `evt_id`
ORDER BY `content`.`content_id` DESC

The issue is that the content_text sent is not the one from the last inserted, but the one for the first inserted row. I tried tu put some Max() in the query but it didn't helped.

Any clue ?

EDIT : data Sample

let's say I have an evt called "pocket" and 3 related content, inputed in this order ("1-cake","2-chocolate","3-sweets").

The result of my query is

evt_id  name    content_id  content_text    evt_cont
149         pocket  112         1-cake          3

What I would like to have :

  evt_id    name    content_id  content_text    evt_cont
    149         pocket  115         3-sweets            3

Answer 1

I'm not working with MySql so there are probably better ways to do this. The simplest way is to join derived table of max (column-representing-order-of-interest, content_id here) by key (content_evt_fk here) to original table filtering out all but last entries.

SELECT 
    evt.evt_id,
    evt.name,
    content.content_id,
    content.content_text
FROM t_evt as evt 
  INNER JOIN t_content AS content
     ON evt.evt_id = content.content_evt_fk
  INNER JOIN 
  (
    select content_evt_fk, max(t_content.content_id) content_id
      from t_content
     group by content_evt_fk
  ) last_content
    ON content.content_id = last_content.content_id
--  This is not necessary here, but if you do this sort of thing on 
--  columns other than id, you will have to join this part too
    and content.content_evt_fk = last_content.content_evt_fk