Select all from table where the rows has the highest id of all those with the same foreign keys

So I have a posts table with a author_id foreign key and a published status (bool).

I want to select all the most recent (highest id) published posts for an author (all those with the same author_id foreign id).

Right now I can get the highest for a group of foreign keys

SELECT * FROM Posts WHERE id = (SELECT MAX(id) FROM Posts WHERE  published = 1 AND author_id = 5); 

But this only returns the highest id for a given foreign key. How would I write this to return all the posts with the highest id in their foreign key group?

Any advice is appreciated. Thanks

EDIT: Had it tagged with sql-server and mysql. It’s mysql. Sorry about that

EDIT: Some asked for clarity Here is a sample of what I’m looking for: id body author_id published 1 Top 10... 1 1 2 Breaking... 1 1 3 New Report.. 3 1 4 Can Sunscreen... 3 1 5 Dow down... 2 1 6 Heart Warming... 2 1 7 Next time... 1 1 8 New study... 3 0

So what i want to do is grab the posts with ids 4, 6, and 7 because 4 is the most recent (highest id) for author 3, 6 is the most recent for author 2 and 7 is the most recent for author 1. I also have the conditional of published which is why we don’t grab 8 because it is 0.

4 Can Sunscreen... 3 1 6 Heart Warming... 2 1 7 Next time... 1 1

Answered: With a slight tweak to Igor Quirino answer by adding an IN instead of =, i think the following works:

SELECT * FROM Posts WHERE id IN (SELECT MAX(id) FROM Posts WHERE published = 1 GROUP BY author_id);

Answer

You should use the IN operator instead of = operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

The IN operator allows you to use a query that returns multiples records (your query must return exactly one column)

SELECT * FROM Posts WHERE id IN (SELECT MAX(id) FROM Posts WHERE published = 1 GROUP BY author_id); 

Happy to Help.