CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
reena.sharam
reena.sharam

Reputation: 135

Sql query group by categoryId having latest publish date

In my table I have to fetch latest article group by there category_id with latest publish date article. I am try this query 

SELECT * FROM news GROUP BY category_id

It gave me first publish articles. How i got article with latest publish date.

Upvotes: 0

Views: 134

Answers (3)

Nicolas78
Nicolas78

Reputation: 5144

This returns the group with the most up-to-date article as the first result:

select category_id, max(publish_date) 
from news 
group by category_id 
order by max(publish_date) desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Here is one version of the query you want:

select n.*
from news n
where not exists (select 1
                  from news n2
                  where n2.category_id = n.category_id and
                        n2.datetime > n.datetime
                 );

This will take advantage of an index on news(category_id, datetime).

Upvotes: 1

juergen d
juergen d

Reputation: 204854

SELECT n1.* 
FROM news n1
join 
(
  select category_id, max(publish_date) as max_publish_date
  from news 
  GROUP BY category_id
) n2 on n1.category_id = n2.category_id 
    and n1.publish_date = n2.max_publish_date

Upvotes: 1

Related Questions