Cross table join grouping not using FK to group

Question

Hi I have 2 tables Entity and Feedback

Lets say that table Feedback has following columns.

id (uuid) 
created_at (datetime) 
entity_id (uuid, FK to entity table)
...

And table Entity has following columns.

id (uuid) 
project_id (uuid)
...

What I need to do is find latest feedback for each entity's project id.

So far I got this:

SELECT f.id, f.created_at, e.id, e.project_id from entity e
RIGHT JOIN feedback f on e.id = f.entity_id

Which gets me list of all entities with feedback, but somehow I need to group it by project_id and get latest feedbackId for that group

http://www.sqlfiddle.com/#!9/fda5cf/1

Edit: Question marked as duplicated by: Selecting from two tables with inner join and limit I think that in this case that solution can not be applied. Reason is that I do not use grouped value as FK between tables, but additional value (project_id) which is contained only in entity table

Edit 2: Added sqlFiddle

well duh, I was trying to point that in the solution grouping was done by column that is common for both tables, In my case I have project_id only in one of the tables. Do you even read the question before commenting? — peter.cambal, Dec 20 '22 at 14:39

score 0 · Answer 1 · answered Dec 20 '22 at 10:06

On MySQL 8+ we can use ROW_NUMBER here:

WITH cte AS (
    SELECT f.id AS f_id, f.created_at, e.id, e.project_id,
           ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY f.created_at DESC) rn
    FROM Entity e
    LEFT JOIN Feedback f ON f.entity_id = e.id
)

SELECT id, f_id, created_at, project_id
FROM cte
WHERE rn = 1;

On earlier versions of MySQL, we can use a join approach:

SELECT f.id AS f_id, f.created_at, e.id, e.project_id
FROM Entity e
LEFT JOIN Feedback f ON f.entity_id = e.id
INNER JOIN
(
    SELECT entity_id, MAX(created_at) AS max_created_at
    FROM Feedback
    GROUP BY entity_id
) t
    ON t.entity_id = f.entity_id AND
       t.max_created_at = f.created_at;

Cross table join grouping not using FK to group

1 Answers1