How to get limited number of distinct records that is above average?-CodePudding

How do I get 2 of the distinct records (including the records with same scores) above the average? Not including the top among rest.

Here's my sample table

id	scores
111	8.50
120	7.45
123	9.13
127	7.70
222	6.00
232	7.77
321	6.80
342	6.90
453	6.66
564	9.05
666	8.50
876	8.90

First, I need to figure out how to get the average. avg(scores) = 7.78

My expected result is:

id	scores
876	8.90
111	8.50
666	8.50

What I have tried so far:

select Examinee_number, score
from examinees
where score > 
    (select avg(score)
    from examinees
    order by score
    limit 2);

select Examinee_number, score
from examinees
where score >
    (select avg(score)
    from examinees)
    order by score desc
    limit 2;

The average should be a reference for scores, in case I only need to get the the scores above the average (score = 8.50) or below the average (score = 7.77).

CodePudding user response：

On MySQL 8 , we can use the RANK() analytic function here. For finding the average score excluding the two top highest, we can try:

WITH cte AS (
    SELECT score, RANK() OVER (ORDER BY score DESC) rnk
    FROM examinees
)

SELECT AVG(score)
FROM cte
WHERE rnk > 2;

CodePudding user response：

select * from `score` where score > (WITH new AS (select * from `score` where score > (SELECT ROUND(AVG(score), 2) FROM `score`) ORDER BY score limit 2) select AVG(score) from new);

select * from score where score < (SELECT AVG(score) FROM `score`) ORDER BY score;