Question 21322 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get limited number of distinct records that is above average? – Mysql

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).

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0 
    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search