DISTINCT across multiple fields?

Let's say I have the following:

[HIGHLIGHT="MySQL"]SELECT DISTINCT art.article_id, art.*
FROM articles AS art
INNER JOIN authors AS auth ON auth.author_id = art.author_id
INNER JOIN article_cats AS ac ON ac.article_id = art.article_id
WHERE ac.category_id IN (1,2,3)
ORDER BY art.post_date DESC
LIMIT 5

An article can be in multiple categories, so I apply the distinct article_id... all is fine.

The problem is, what if I want unique author too? So, say I wanted the 5 latest articles, where each is from a different author... (the lastest 5 posts from all authors, 1 post per author...)

How would I do that?

Thanks!

 

Try throwing a GROUP BY in the query.

GROUP BY art.author_id
ORDER BY art.post_date DESC

 

GROUP happens before ORDER, which massively restricts things and causes what I want not to work

 

the DISTINCT keyword will only work for the first attribute by default...
Group by and order by can be used here in the end of correlated query.

 

The syntax of group by comes before order by, but the actual order in which mysql processes should still work for what you are needing.

As written above with a group by, the query will select the 5 most recent articles, one for each author, ordered by the most recent date.