0

I'm writing a query in which i want to limit the number of rows by foreign key id.

To give you an example let's say i have a posts table and each post has anywhere from 0 to 100 comments.

Now right now I'm looping over all posts individually and then SELECT the comments for each post using a LIMIT 0,5.

What i want to do instead is just write one SELECT statement for reading all the comments using something like post_id IN (1,2,3,4...999) but only load up to 5 comments per each post?

Edit: The postgres way of doing it can be found here. The mysql way of doing it is here

supersan
  • 5,671
  • 3
  • 45
  • 64
  • Which database are you using? In postgresql you would do this using a window function or a lateral join. Anyway, this sort of query is usually called a `top-N per group` query, if you're looking to google it. – Marth Aug 22 '17 at 19:01
  • I'm using mysql right now but if postgres supports it i would be very eager to learn it too – supersan Aug 22 '17 at 19:03
  • Would you please show your table structure? Is there any column that determines which first 5 comment should be? A comment id perhaps? – Ferdinand Gaspar Aug 22 '17 at 19:06
  • Please update your post and provide us from sample data / table structure and desired result – Noob Aug 22 '17 at 19:08
  • for postgres you use `row_number()` https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group – Juan Carlos Oropeza Aug 22 '17 at 19:12
  • @FerdinandGaspar post table contains `post_id`, `date` and `title`, comments table contains `comment_id`, `post_id`, `date` and `text`. I want to get the last 5 comments (i.e. `comments.date DESC`) – supersan Aug 23 '17 at 07:30
  • @JuanCarlosOropeza thanks for the links – supersan Aug 23 '17 at 07:32

0 Answers0