Average and group by in SQL but for best 10 records only

Question

Given: A ranking table (id, user_id, score, group_id, date)

Currently we calculate a ranking based on all participating users based on sum and average.

SELECT
    ROUND(AVG(r.score)::NUMERIC, 2) AS score,
    SUM(score) AS score_sum,
    MAX(r.date) AS ranking_timestamp,
    a.name AS group_name,
    a.id AS group_id
FROM
ranking r, group a
WHERE a.id = r.group_id
GROUP BY a.id,a.name
ORDER BY AVG(r.score) DESC,MAX(r.date) ASC

Now we want to change that. Instead of honor all participating user, take the 10 best users only, calculate SUM and AVG.

Is that possible within one statement?

Answer 1

you can do this:

WITH TEMP AS
    (
        SELECT
            ROUND(AVG(r.score)::NUMERIC, 2) AS score,
            SUM(score) AS score_sum,
            MAX(r.date) AS ranking_timestamp,
            a.name AS group_name,
            a.id AS group_id
        FROM
        ranking r, group a
        WHERE a.id = r.group_id
        GROUP BY a.id,a.name
        ORDER BY AVG(r.score) DESC,MAX(r.date) ASC
    )


SELECT TOP 10 * FROM TEMP ORDER BY score ASC