0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

SELECT a.id, a.name, a.skaits
FROM
(
SELECT p.id, vt.name, COUNT(1) AS skaits,
ROW_NUMBER() OVER(ORDER BY COUNT(1) DESC) AS rn
FROM posts p
JOIN votes v
ON p.id = v.postid
JOIN votetypes vt
ON v.votetypeid = vt.id
WHERE UPPER(vt.name) = 'UPMOD'
GROUP BY p.id, vt.name) AS a
WHERE a.rn <= 10


UNION
SELECT s.id, s.name, s.skaits
FROM
(
SELECT p.id, vt.name, COUNT(1) AS skaits,
ROW_NUMBER() OVER(ORDER BY COUNT(1) DESC) AS rn
FROM posts p
JOIN votes v
ON p.id = v.postid
JOIN votetypes vt
ON v.votetypeid = vt.id
WHERE UPPER(vt.name) = 'DOWNMOD'
GROUP BY p.id, vt.name) AS s
WHERE s.rn <= 10

ORDER BY a.skaits DESC

fork query

edited mar 27 23

created mar 23 23 anonymous