This question already has answers here:
SQL select only rows with max value on a column [duplicate] (27 answers)
Selecting from two tables with inner join and limit [duplicate] (5 answers)
Closed yesterday.
The community is reviewing whether to reopen this question as of yesterday.
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
1条答案
按热度按时间oxf4rvwz1#
On MySQL 8+ we can use
ROW_NUMBER
here:On earlier versions of MySQL, we can use a join approach: