CODEWITHSUNDEEP
phpmysql
naphier
naphier

Reputation: 276

MySQL How to obtain the MIN and MAX of the top 100 values in a table

I want to be able to find the maximum and minimum value of the top 100 scores in our database.

It seems like it should be simple, but I'm missing something.

I've tried 

SELECT 
       MAX(BEST_GAME_SCORE) as max_bgs , 
       MIN(BEST_GAME_SCORE) as min_bgs 
FROM user 
WHERE BEST_GAME_SCORE IN (SELECT BEST_GAME_SCORE 
                          FROM user 
                          ORDER BY BEST_GAME_SCORE DESC 
                          LIMIT 100)

but... LIMIT doesn't work in a subquery

I've tried using JOIN, but I always get 0 for the MIN where min in the join should not be 0

SELECT 
    MAX(user.BEST_GAME_SCORE), 
    MIN(user.BEST_GAME_SCORE) 
FROM user 
  JOIN ( SELECT user.BEST_GAME_SCORE 
         FROM user 
         ORDER BY user.BEST_GAME_SCORE DESC 
         LIMIT 100) latest

EDIT: FOR EXAMPLE If I have a table with the following scores

1000
900
800
700
600
500
400
300
200
100

I would like to select the top 5 scores (i.e. 1000,900,800,700,600) but have the query simply return the max and min of that so 1000 and 600. Sorry I thought that was clear.

Upvotes: 0

Views: 279

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use a subquery:

SELECT MAX(BEST_GAME_SCORE) as max_bgs , MIN(BEST_GAME_SCORE) as min_bgs
from (SELECT BEST_GAME_SCORE
      FROM user
      ORDER BY BEST_GAME_SCORE DESC
      LIMIT 100
     ) u;

Upvotes: 3

Related Questions