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

Question

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.

@RahulBiswas did you remove your answer? I was commenting that MAX(id) is not working because they are NOT incremental. So, id 7 can be earlier than id 3. — trogwar, Mar 25 '22 at 08:58

Rahul Biswas · Accepted Answer · 2022-03-25T09:05:45.643

1

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

edited Mar 25 '22 at 09:05

answered Mar 25 '22 at 08:40

Rahul Biswas

3,207
2
10
20

Thank you for a well explained answer! But, sorry, it’s not working properly. – trogwar Mar 25 '22 at 08:49
1

please check this answer. – Rahul Biswas Mar 25 '22 at 09:16
1

Oh wow, thank you for help! I would never have thought of `OVER`, `PARTITION BY` and `ROW_NUMBER`. Works great, and will research docs for those features. – trogwar Mar 25 '22 at 09:21

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

1 Answers1