CODEWITHSUNDEEP
mysqlsqlsubquery
Steve
Steve

Reputation: 1211

GROUP BY and get the top two row

So I have the following query where it does as i expect it to. It get the latest published article from each aothor. However now I want it to get the latest two article from each author. How can I do this?

SELECT author_article.ID
FROM (
       SELECT
         sorted_articles.ID,
         sorted_articles.AuthorID,
         sorted_articles.PublishedDate
       FROM ArticlePage sorted_articles
       ORDER BY PublishedDate DESC
     ) author_article
GROUP BY author_article.AuthorID
ORDER BY author_article.PublishedDate DESC;

enter image description here

So what I need is the latest 2 article for each author.

Upvotes: 0

Views: 60

Answers (3)

Ullas
Ullas

Reputation: 11556

Just an other perspective by giving a row_number.

Query

select t2.articleId,
t2.articleName,
t2.authorName,
t2.publishedDate
from
(
   select articleId,
   articleName,
   authorName,
    publishedDate,
    ( 
        case authorName 
        when @curA
        then @curRow := @curRow + 1 
       else @curRow := 1 and @curA := authorName end
    ) + 1 as rn
    from article t,
    (select @curRow := 0, @curA := '') r
    order by authorName,publishedDate desc
)t2
where t2.rn<3;

Fiddle demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you want the authors and the article ids, then this will put them in one row:

SELECT ap.AuthorId,
       SUBSTRING_INDEX(GROUP_CONCAT(ap.AuthorId ORDER BY ap.PublishedDate DESC
                                   ), ',', 2) as Top2Articles
FROM ArticlePage ap
GROUP BY ap.AuthorId;

Note: the default length for the group concat intermediate value is limited, but can be changed if some authors have lots and lots and lots of articles.

Also, your original query is using a (mis)feature of MySQL that is explicitly documented not to work as you intend. You have columns in the SELECT that are not in the GROUP BY. These values come from indeterminate rows, so the ORDER BY in the subquery may not affect the results the way you intend.

Upvotes: 1

jarlh
jarlh

Reputation: 44795

Use a correlated sub-query to count all more recent articles by the same author. If there are 1 or less more recent articles, return the row.

SELECT *
FROM ArticlePage t1
WHERE (select count(*) from ArticlePage t2
       where t2.AuthorID = t1.AuthorID
         and t2.PublishedDate > t1.PublishedDate) <= 1

Upvotes: 3

Related Questions