Exclude the Two Highest Outliers from Query

The query at http:Q_22463410.html will exclude the row with an outlier (the 1 highest number) in a particular column, like this:

$sql = 'SELECT ID, Student, Score FROM scores t1 WHERE t1.Student = "Doe" AND t1.Score != (SELECT MAX(Score) FROM scores t2 WHERE t2.Student="Doe") ORDER BY Score ASC';

Is it possible to exclude the *two* rows with the highest numbers?  For example, if the scores in that column are:  500, 499, 100, 99, 100, 98, 97, 95, I would need the query to exclude both the 500 and the 499.  (I don't want to use any arbitrary cutoffs, because the numbers will vary greatly depending on the target.)
Randall-BAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
query1:
CREATE TABLE tmp_StudentScore
SELECT Score FROM scores t2 WHERE t2.Student="Doe" ORDER BY score DESC LIMIT 2

query2:
SELECT ID, Student, Score FROM scores t1 WHERE t1.Student = "Doe" AND t1.Score NOT IN (select Score from tmp_StudentScore ) ORDER BY Score ASC

query3:
DROP TABLE tmp_StudentScore

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
$sql = 'SELECT ID, Student, Score FROM scores t1 WHERE t1.Student = "Doe" AND t1.Score NOT IN (SELECT Score FROM scores t2 WHERE t2.Student="Doe" ORDER BY score DESC LIMIT 2) ORDER BY Score ASC';
0
 
Randall-BAuthor Commented:
angelIII,
   Thanks. But I'm getting an error:
     "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' "
   I'm using the latest version of WAMP. I think it's a 4.xx version of MySQL.  Is there any way around this limitation?  
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, unless you create temporary table with the subquery result
0
 
Randall-BAuthor Commented:
OK. Then please explain how to create and use a temporary table in this situation. Thanks.
0
 
Randall-BAuthor Commented:
That works for me.  Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.