How to change this SQL so that I can get one record from each author?

Question

In sql below, I have the last 5 posts. But sometimes these messages belong to one author. I want to select the last 5, but only one for the author.

SELECT `e` . * , 
       `f`.`title` AS `feedTitle` , 
       `f`.`url` AS `feedUrl` , 
       `a`.`id` AS `authorId` , 
       `a`.`name` AS `authorName` , 
       `a`.`about` AS `authorAbout`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categories` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categories` AS `c` ON ec.category_id = c.id
INNER JOIN `authors` AS `a` ON e.author_id = a.id
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
LIMIT 5 

EDITED

I ended up with this:

SELECT a.id, e.date, e.title, a.name
FROM feed_entries e, authors a 
WHERE e.author_id =a.id 
ORDER BY e.date DESC 
LIMIT 5

In this question, how can I get only one post for each author?

Answer 1

I have anwser here: DISTINCT column in database

I made some changes and worked great.

SELECT * FROM (
SELECT `e` . * , 
       `f`.`title` AS `feedTitle` , 
       `f`.`url` AS `feedUrl` , 
       `a`.`id` AS `authorId` , 
       `a`.`name` AS `authorName` , 
       `a`.`about` AS `authorAbout`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categories` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categories` AS `c` ON ec.category_id = c.id
INNER JOIN `authors` AS `a` ON e.author_id = a.id
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
) t GROUP BY author_id ORDER BY date DESC 
LIMIT 5 

Answer 2

What about

select a.id, a.name, e.date, e.titulo
  from feed_entries e
 inner join authors a
    on e.author_id = a.id
    -- Get the most recent feed_entry
   and e.date = (select max(e1.date) from feed_entries e1 where e1.author_id = a.id)
 order by e.date desc

I did not test this, but it could work.

Answer 3

- ( ):

SELECT `e` . * , `f`.`title` AS `feedTitle` , `f`.`url` AS `feedUrl` , `a`.`id` AS `authorId` , `a`.`name` AS `authorName` , `a`.`about` AS `authorAbout`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categories` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categories` AS `c` ON ec.category_id = c.id
INNER JOIN `authors` AS `a` ON e.author_id = a.id
INNER JOIN ( SELECT TOP 1 entry_id, author_id 
             FROM feed_entries 
             GROUP BY author_id 
             ORDER BY date DESC) as top_article
ON (a.id = top_article.author_id)
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
LIMIT 5