Take the 2-minute tour ×


  • I have an app that shows posts and comments on the home page.
  • My intention is to limit the number of posts shown (ie, 10 posts) and...
  • Limit the number of comments shown per post (ie, 2 comments).
  • Show the total number of comments in the front end (ie, "read all 10 comments")


         FROM   post
         ORDER  BY post_timestamp DESC
         LIMIT  0, 10) AS p
        JOIN user_profiles
          ON user_id = p.post_author_id
                   FROM   data
                          JOIN pts
                            ON pts_id = pts_id_fk) AS d
          ON d.data_id = p.data_id_fk
                   FROM   comment
                          JOIN user_profiles
                            ON user_id = comment_author_id
                   ORDER  BY comment_id ASC) AS c
          ON p.post_id = c.post_id_fk))

I've failed to insert LIMIT and COUNT in this code to get what I want - any suggestions? - will be glad to post more info if needed.

share|improve this question
Please show your schema. It's difficult to reverse engineer a non-working query. –  Marcus Adams Mar 11 '12 at 3:52
add comment

2 Answers

up vote 0 down vote accepted

If I'm understanding you correctly you want no more than 10 posts (and 2 comments) to come back for each unique user in the returned result set.

This is very easy in SQLServer / Oracle / Postgre using a "row_number() PARTITION BY".

Unfortunately there is no such function in MySql. Similar question has been asked here:

I'm sorry I can't offer a more specific solution for MySql. Definitely further research "row number partition by" equivalents for MySql.

The essence of what this does:

You can add a set of columns that make up a unique set, say user id for example sake (this is the "partition") A "row number" column is then added to each row that matches the partition and starts over when it changes.

This should illustrate:

user_id  row_number
1        1
1        2
1        3
2        1
2        2

You can then add an outer query that says: select where row_number <= 10, which can be used in your case to limit to no more than 10 posts. Using the max row_number for that user to determine the "read all 10 comments" part.

Good luck!

share|improve this answer
add comment

This is the skeleton of the query you're looking for:

select * from (
  select p1.id from posts p1
  join posts p2 on p1.id <= p2.id
  group by p1.id
  having count(*) <= 3
  order by p1.post_timestamp desc
) p left join (
  select c1.id, c2.post_id from comments c1
  join comments c2 on c1.id <= c2.id and c1.post_id = c2.post_id
  group by c1.id
  having count(*) <= 2
  order by c1.comment_timestamp desc
) c
on p.id = c.post_id

It will get posts ordered by their descending timestamp but only the top 3 of them. That result will be joined with the top 2 comments of each post order by their descending timestamp. Just change the column names and it will work :)

share|improve this answer
add comment

Your Answer


