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?

perhaps one approach is to use a sub query to get the best 10 and then run the grouping query. — leoh, Oct 24 '16 at 12:38

score 1 · Accepted Answer · edited Oct 24 '16 at 14:14

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

score 0 · Answer 2 · answered Oct 24 '16 at 12:38

Add TOP 10

SELECT TOP 10
    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

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

2 Answers2