List:General Discussion« Previous MessageNext Message »
From:Ken Gieselman Date:March 1 2005 10:07pm
Subject:Complicated Subquery help
View as plain text  
Hiya Folks!

I'm struggling with a complicated subquery issue, and my SQL-fu isn't strong
enough to make a solution apparent.  Hopefully one of you experts can give me a
nudge in the right direction :)

The query utilizes data from just one table, which looks like:

idX                  integer
idY                  tinyint unsigned,
idZ                  tinyint,
c1                   smallint,
c2                   smallint,
c3                   smallint,
     ... [and so on] ...

What I'd like to do is select a set of the channel data (the c* fields) based on
rows where the standard deviation on a given channel is below a certain
threshold for the rows "near" that one, based on the values of the id fields.

Getting the standard deviation on a single field for a given row isn't too bad:

select std(c2330) from radiances where idZ between 44 and 46 and idX between 12
and 14, and idX = 7;

That query selects the standard deviation for channel 2330, in the 9
"footprints" around the current point I'm looking at.  Selecting the channels I
need to analyze based on that should just using that select inside an IF

The trick is, how to automate this to iterate over all the id* fields?  idX is
an auto_increment counter based on observation time, idY and idZ are tied to
specific observations within a given set, and are integer counters that vary
from 0-150 or so.

Is there a way to do this with one (or more) sql queries, or would I be better
off writing a specific program for doing the selection?

Thanks for the help!
              "Diplomacy is the weapon of the Civilized Warrior"
                                    - Hun, A.T.

Ken Gieselman                                      kgieselm@stripped
System Administrator         

Complicated Subquery helpKen Gieselman1 Mar