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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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]Connect With a Mentor 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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
All Courses

From novice to tech pro — start learning today.