If you can assume that artist IDs increment over time, then the MIN(artist_id) will be the earliest. So try something like this (untested…) SELECT * FROM feeds f LEFT JOIN artists a ON a.artist_id = ( SELECT MIN(fa.artist_id) a_id FROM feeds_artists fa WHERE fa.feed_id = f.feed_id ) a
MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; This and other approaches … Read more
At first glance… All you need is a GROUP BY clause with the MAX aggregate function: SELECT id, MAX(rev) FROM YourTable GROUP BY id It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row … Read more