How to limit query selection with join tables on one table in SQL

Question

SQL

There are two tables with a one-to-many relationship: the articles table with articles and the comments table with the corresponding comments. The usual selection of them is made as follows (the latest articles and the first comments on them are selected):

SELECT
  articles.title,
  articles.date,
  comments.author,
  comments.text
FROM
  articles
  INNER JOIN comments ON comments.article = articles.id
ORDER BY
  articles.id DESC,
  comments.id ASC

How to make a request so that:

1. Was only the required number of articles selected (for example, 10) with all the comments?
2. Was there a limited number of comments for each article?
3. Was a limited number of articles selected and a limited number of comments on them?

I don't know if it's possible to do this in one request, but maybe... Any help would be greatly appreciated.

Answer 1

1. Instead of the usual join, we do a slightly unusual one:
SELECT *
FROM comments as c
INNER JOIN (SELECT * FROM articles LIMIT 10) as a
ON a.id = c.aticle_id
2. No way with one query.
3. One request does not.
2 and 3 is done by simply selecting articles with or without limit, and then fetching comments for each article with or without limit.

Answer 2

Look towards top or limit commands

Answer 3

For 2 and 3 you can do this:

SELECT   t1.title, t1.date, t1.author, t1.text  
FROM         
(SELECT     articles.title, articles.date, comments.author, comments.text, articles.id as aid, comments.id as cid, DENSE_RANK() OVER   ( ORDER BY articles.id DESC) as article_row, row_number () over (partition by articles.id order by comments.id) as comment_row
FROM    articles INNER JOIN  comments ON articles.id = comments.article   ) AS t1
WHERE t1.article_row<=2 and t1.comment_row<=5
ORDER BY t1.aid DESC, t1.cid

Although this option is not optimal in terms of speed, because in fact, articles and comments on each article are numbered first, and then the excess is cut off.