How to get non grouped data of last row by datetime with multiple joins?-CodePudding

There is tickets, they always contain some comments (at least one) and table to conect them. All PKs are numeric but randomized, so, timeline based on DATETIME field of ticket/comment table.

I need to list of tickets (id, author) with all LAST (based on datetime field) comment data (id, date, author, text) and aggregated field (how much comments on ticket).

Prepared DB Fiddle is here: https://www.db-fiddle.com/f/3PUfo2t4JkEMSA3Skw4sua/1

Raw SQL query here:

SELECT
    tc.ticket_id        AS ticket_id
    , COUNT(c.id)       AS comment_count
    , MAX(c.created_at) AS last_at
    , c.author_id       AS last_author_id
    , c.text            AS last_text
FROM `ticket_comment` AS tc
JOIN `comment`        AS c  ON c.id = tc.comment_id
GROUP BY tc.ticket_id
ORDER BY last_at DESC

I've got "ER_WRONG_FIELD_WITH_GROUP" error and i understand why it happens, but have no idea how fix it.

I tried to turn off ONLY_FULL_GROUP_BY setting but the result will lead to bad data.

Expected data:

ticket_id	comment_count	last_at	last_author_id	last_text
974442	2	2022-01-01 00:55:55	22222	t3c2
462230	2	2022-01-01 00:33:33	11111	t1c2
490694	1	2022-01-01 00:22:22	22222	t2c1

What i've got:

ticket_id	comment_count	last_at	last_author_id	last_text
974442	2	2022-01-01 00:55:55	11111	t3c1
462230	2	2022-01-01 00:33:33	11111	t1c2
490694	1	2022-01-01 00:22:22	22222	t2c1

Notice the difference in all fields except ticket_id and last_at – all wrong.

CodePudding user response：

Use subquery for retrieving ticket_id wise comment count and which comments come last based on created date DESC by using ROW_NUMBER().

-- MariaDB (10.4)
    SELECT p.ticket_id, p.comment_count
     , p.created_at last_at
     , p.author_id last_author_id
     , p.text last_date
FROM (SELECT *
           , COUNT(c.id) OVER(PARTITION BY ticket_id) comment_count
           , ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY c.created_at DESC) r
      FROM ticket_comment t
      INNER JOIN comment c
              ON c.id = t.comment_id) p
WHERE p.r = 1
ORDER BY p.comment_count DESC
       , p.ticket_id DESC

Please check this url https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=b461286719332ec3592d40a246ae3bf6