0

I have a blog where multiple authors can post. On the frontpage I want to show a carousel of the most recent X posts - let's say 5. However, I want to filter out author duplicates. So if an author would have multiple posts within those 5, only the most recent one would make it in and the others would be filtered out. Also, I want to select the entire row, not just IDs. Here an example:

Data

ID | Title   | AuthorID | Date
1  | "Now"   | 6        | 2013-03-27
2  | "this"  | 5        | 2013-03-26
3  | "is"    | 4        | 2013-03-27
4  | "the"   | 2        | 2013-03-28
5  | "story" | 2        | 2013-03-29
6  | "all"   | 4        | 2013-04-01
7  | "about" | 2        | 2013-04-02
8  | "how"   | 3        | 2013-04-03
9  | "My"    | 1        | 2013-04-04
10 | "life"  | 1        | 2013-04-05

Desired result

ID | Title   | AuthorID | Date
10 | "life"  | 1        | 2013-04-05
8  | "how"   | 3        | 2013-04-03
7  | "about" | 2        | 2013-04-02
6  | "all"   | 4        | 2013-04-01
2  | "this"  | 5        | 2013-03-26

I currently use the following:

SELECT * FROM posts
ORDER BY date DESC
LIMIT 5

But of course this gets me the following result

ID | Title   | AuthorID | Date
10 | "life"  | 1        | 2013-04-05
9  | "My"    | 1        | 2013-04-04
8  | "how"   | 3        | 2013-04-03
7  | "about" | 2        | 2013-04-02
6  | "all"   | 4        | 2013-04-01

and I want to eliminate the duplicates. How?

I saw that there is a DISTINCT clause but this would select only the author IDs and I want to select the entire row.

Krystian
  • 45
  • 1
  • 4
  • Check out this post: http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns – Niro Apr 05 '13 at 19:48
  • @orin shabbat shalom!!!! – Alex Gordon Apr 05 '13 at 19:49
  • Probably the fastest is a convoluted `GROUP BY author_id`, with a `SUBSTRING_INDEX(GROUP_CONCAT(field.... ORDER BY date))` kind of construct for the actual posts. – Wrikken Apr 05 '13 at 19:49

2 Answers2

1

This trick/hack will work, but might take some time to generate results(took me 3.713 seconds on a table with 1,00,000 rows):

SELECT *
FROM (SELECT * 
    FROM posts
    ORDER BY date DESC ) d
GROUP BY d.`AuthorID`
LIMIT 5;
hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • That doesn't seem to deliver the most recent results. In some cases I even seem to get the first posts by the authors instead of the recent one. I think it's grouping before it orders. – Krystian Apr 05 '13 at 20:00
  • 1
    @hjpotter92 Can we be **100%** sure that with this (legal in MySql) approach we will not get a title with the **wrong** date? – PM 77-1 Apr 05 '13 at 20:04
  • @hjpotter92 Ah! So that's how it works! Thanks, it is what I was looking for. It's almost the same solution as Mary's. Selected his answer due to the SQL Fiddle. I hope you don't mind. – Krystian Apr 05 '13 at 20:31
0

I think this is what you're looking for:

SELECT *
FROM (SELECT *
      FROM posts
      ORDER BY Date DESC) p
GROUP BY p.AuthorID
ORDER BY Date DESC
LIMIT 5;

SQL Fiddle

Marty McVry
  • 2,838
  • 1
  • 17
  • 23
  • Yes, that's the ticket. The Fiddle helps a lot. I like how the dates are a bit different than in the above example to make sure it's not sorting my most recent ID but the actual date field. Thank you! – Krystian Apr 05 '13 at 20:32