Select latest post from each categories

Question

Am trying to get the latest post from each category of post ordered by the date, i tried this but its not giving the latest post from the table and when i use 'order by' before 'group by', the post are not ordered by date using mysql.

SELECT post_id, category, author_id, title, article, time FROM (SELECT * FROM blog_post GROUP BY category LIMIT 0,5 ) AS timePost ORDER BY time DESC

SELECT post_id, category, author_id, title, article, time FROM (SELECT * FROM blog_post ORDER BY date LIMIT 0,5 ) AS timePost GROUP BY category

which database type are you using? oracle, mysql, sql-server, .... — Thomas, Nov 24 '15 at 06:20
Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Strawberry, Nov 24 '15 at 07:16

score 1 · Answer 1 · answered Nov 24 '15 at 06:58

You can do self join.

   SELECT T1.`post_id`,
           T1.`category`,
            T1.`author_id`,
             T1.`title`, 
             T1.`article`,
              T1.`time` 
    FROM
            blog_post T1
            INNER JOIN
            (SELECT MAX(`time`) AS `time`,`category` FROM blog_post GROUP BY category) T2
         ON T1.`category` = T2.`category` AND T1.`time` = T2.`time`
  ORDER BY T1.`time` DESC

Hope this helps.

score 0 · Answer 2 · answered Nov 24 '15 at 06:23

IN MS SQL as your question is not clear as per my assumption

SELECT post_id, category, author_id, title, article, time
FROM blog_post t
WHERE 
 t.post_id=(SELECT TOP 1 post_id
          FROM Table t2
          WHERE 
           t.post_id=t2.post_id
          ORDER BY time DESC)

score -1 · Answer 3 · answered Nov 24 '15 at 06:59

-1

This seem to have work... i dont know how efficient it is though

SELECT post_id, category, author_id, title, article, time FROM (SELECT * FROM blog_post ORDER BY time DESC ) AS timePost GROUP BY category ORDER BY time DESC

answered Nov 24 '15 at 06:59

cozmik05

477
5
13

This will display all the posts, but you need the latest one only? – Subin Chalil Nov 24 '15 at 07:13

Select latest post from each categories

3 Answers3