MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE articles (
    id INTEGER PRIMARY KEY
);

CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    article_id INTEGER,
    votes INTEGER
);

INSERT INTO articles (id) 
VALUES 
  (1), 
  (2);

INSERT INTO comments (id, article_id, votes)
VALUES 
  (1, 1, 1), 
  (2, 1, 2), 
  (3, 1, 3), 
  (4, 1, 4), 
  (5, 2, 10), 
  (6, 2, 9), 
  (7, 2, 8), 
  (8, 2, 7);
-- wrong row numbers
SELECT
  comments.id,
  comments.article_id,
  comments.votes,
  COUNT(DISTINCT articles.id) AS aggregated,
  ROW_NUMBER() OVER (
    PARTITION BY
      comments.article_id
    ORDER BY
      comments.votes DESC
    ) AS row_num
FROM
  comments
  LEFT JOIN articles ON articles.id = comments.article_id
WHERE
  comments.article_id IN (1, 2)
GROUP BY
  comments.id,
  articles.id;


id
article_id
votes
aggregated
row_num


1
1
1
1
3

2
1
2
1
2

3
1
3
1
1

4
1
4
1
1

5
2
10
1
2

6
2
9
1
3

7
2
8
1
4

8
2
7
1
4


-- correct row numbers without grouping on articles.id
SELECT
  comments.id,
  comments.article_id,
  comments.votes,
  COUNT(DISTINCT articles.id) AS aggregated,
  ROW_NUMBER() OVER (
    PARTITION BY
      comments.article_id
    ORDER BY
      comments.votes DESC
    ) AS row_num
FROM
  comments
  LEFT JOIN articles ON articles.id = comments.article_id
WHERE
  comments.article_id IN (1, 2)
GROUP BY
  comments.id;


id
article_id
votes
aggregated
row_num


4
1
4
1
1

3
1
3
1
2

2
1
2
1
3

1
1
1
1
4

5
2
10
1
1

6
2
9
1
2

7
2
8
1
3

8
2
7
1
4


-- correct row numbers with non-distinct counting
SELECT
  comments.id,
  comments.article_id,
  comments.votes,
  COUNT(articles.id) AS aggregated,
  ROW_NUMBER() OVER (
    PARTITION BY
      comments.article_id
    ORDER BY
      comments.votes DESC
    ) AS row_num
FROM
  comments
  LEFT JOIN articles ON articles.id = comments.article_id
WHERE
  comments.article_id IN (1, 2)
GROUP BY
  comments.id,
  articles.id;


id
article_id
votes
aggregated
row_num


1
1
1
1
4

2
1
2
1
3

3
1
3
1
2

4
1
4
1
1

5
2
10
1
1

6
2
9
1
2

7
2
8
1
3

8
2
7
1
4


-- correct row numbers with min value aggregation instead of counting
SELECT
  comments.id,
  comments.article_id,
  comments.votes,
  MIN(DISTINCT articles.id) AS aggregated,
  ROW_NUMBER() OVER (
    PARTITION BY
      comments.article_id
    ORDER BY
      comments.votes DESC
    ) AS row_num
FROM
  comments
  LEFT JOIN articles ON articles.id = comments.article_id
WHERE
  comments.article_id IN (1, 2)
GROUP BY
  comments.id,
  articles.id;


id
article_id
votes
aggregated
row_num


1
1
1
1
4

2
1
2
1
3

3
1
3
1
2

4
1
4
1
1

5
2
10
2
1

6
2
9
2
2

7
2
8
2
3

8
2
7
2
4


-- correct row numbers whith order on the otherwise wrong row number
SELECT
  comments.id,
  comments.article_id,
  comments.votes,
  COUNT(DISTINCT articles.id) AS aggregated,
  ROW_NUMBER() OVER (
    PARTITION BY
      comments.article_id
    ORDER BY
      comments.votes DESC
    ) AS row_num
FROM
  comments
  LEFT JOIN articles ON articles.id = comments.article_id
WHERE
  comments.article_id IN (1, 2)
GROUP BY
  comments.id,
  articles.id
ORDER BY
  row_num ASC;


id
article_id
votes
aggregated
row_num


4
1
4
1
1

5
2
10
1
1

3
1
3
1
2

6
2
9
1
2

2
1
2
1
3

7
2
8
1
3

1
1
1
1
4

8
2
7
1
4