Select N Records For Each Category And Order By X

I have a database table that contains blog posts. I want to show on the homepage one (or more) post for each category, ordering by date, for example. So my posts table looks like

Solution 1:

MySQL doesn't support analytic functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE...), but you can emulate the functionality with variables.

If you want the N most recent blog posts:

SELECT x.id,
       x.title,
       x.description,
       x.cat,
       x.filename,
       x.date
  FROM (SELECT bp.id,
               bp.title,
               bp.description,
               bp.cat,
               bp.filename,
               bp.date,
               CASEWHEN bp.cat =@categoryTHEN@rownum :=@rownum+1ELSE@rownum :=1ENDAS rank,
               @category := bp.cat
          FROM BLOG_POSTS bp
          JOIN (SELECT@rownum :=0, @category :=NULL) r
      ORDERBY bp.cat, bp.date DESC) x
 WHERE x.rank <= N

If you want rank of 1 to be the earliest blog post, change the ORDER BY to:

ORDERBY bp.cat, bp.date

Solution 2:

With more modern SQL, using CTE and Windows Functions (tested in PostgreSQL 9.3, but I suspect it'll work on a recent version of MySQL too), to show say 2 title per category:

WITH b AS
  (SELECT title, cat, row_number() OVER (PARTITIONBY cat) as rn FROM BLOGS)
SELECT title, cat FROM b WHERE rn <=2;