Solved

Exclude the Two Highest Outliers from Query

Posted on 2007-03-21
6
472 Views
Last Modified: 2012-05-05
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.)
0
Comment
Question by:Randall-B
  • 3
  • 3
6 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18765922
$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
 

Author Comment

by:Randall-B
ID: 18766036
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766088
no, unless you create temporary table with the subquery result
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Randall-B
ID: 18766162
OK. Then please explain how to create and use a temporary table in this situation. Thanks.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18766196
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
 

Author Comment

by:Randall-B
ID: 18766351
That works for me.  Thanks so much.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now