MySQL - COUNT and retrieve n rows from a subquery

Question

Context:

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")

MySQL:

(SELECT *
 FROM   (SELECT *
         FROM   post
         ORDER  BY post_timestamp DESC
         LIMIT  0, 10) AS p
        JOIN user_profiles
          ON user_id = p.post_author_id
        LEFT JOIN (SELECT *
                   FROM   data
                          JOIN pts
                            ON pts_id = pts_id_fk) AS d
          ON d.data_id = p.data_id_fk
        LEFT JOIN (SELECT *
                   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.

Please show your schema. It's difficult to reverse engineer a non-working query. — Marcus Adams, Mar 11 '12 at 03:52

score 0 · Accepted Answer · edited May 23 '17 at 12:11

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:
ROW_NUMBER() in MySQL

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!

score 0 · Answer 2 · answered Mar 11 '12 at 03:56

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 :)

MySQL - COUNT and retrieve n rows from a subquery

2 Answers2