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

Issue

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.

Solution

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

Answered By – Rahul Biswas

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.