What query works in place of LEAST and GREATEST

  • Thread starter Thread starter Cross-eyed
  • Start date Start date
C

Cross-eyed

I have a table of over 6,000 records. For one of my queries I am havin
to return the rows that have a "fold change" greater than som
predefined number (1.5, 2, 3, or 4). A fold change is determined b
dividing the largest number in an row by the smallest number in a row.

For example:

---------------------------------
ID | test_1 | test_2 | test_3
---------------------------------
exp_1 | 18 | 25 | 30
exp_2 | 52 | 127 | 39

Experiment 1 has a fold change of 1.67. This is done by taking th
largest value of the three tests (30), and a dividing it by th
smallest of the three (18). (30/18 = 1.66667)

Experiment 2 has a fold change of 3.26. (127/39 = 3.2564)

I need a SELECT statement that would return all (*) information on
row WHERE the fold change is greater than or equal to (>=) th
predefined number.


For instance:

---------------------------------
ID | test_1 | test_2 | test_3
---------------------------------
exp_1 | 18 | 25 | 30
exp_2 | 52 | 127 | 39
exp_3 | 24 | 12 | 52
exp_4 | 7 | 23 | 18
exp_5 | 87 | 123 | 80
exp_6 | 79 | 39 | 41
exp_7 | 24 | 12 | 5
exp_8 | 74 | 78 | 82

Let's say the predefined number was 3.
-Therefore the SQL Query would return all (*) information for exp_2
exp_3, exp_4, and exp_7.

If the predefined number was 4 then,
-the SQL Query would return all information for exp_3 and exp_7

If the predefined number was 1.5 then it would return all rows excep
exp_8.

The following query would work but I can't use LEAST and GREATEST. I'
running this as a web application using ASP and an Access database.

SELECT * FROM my_table WHERE
GREATEST(test_1, test_2, test_3)/LEAST(test_1, test_2, test_3)
:predefined_number;

Does anyone have any suggestions
 
Apparently Steve has a solution. I would reconsider the table structure to
make this a fairly simple SQL exercise. Each of your values should create
its own record in a table rather than having field names like
Test_1,...Test_x

tblTestResults
===============
ID
Test
Value
 
I have a table of over 6,000 records. For one of my queries I am having
to return the rows that have a "fold change" greater than some
predefined number (1.5, 2, 3, or 4). A fold change is determined by
dividing the largest number in an row by the smallest number in a row.

For example:
Click to expand...

Well... this table violates second normal form. If you get a Test_4,
won't you need to redesign all your Tables, Forms, Queries, Reports to
accommodate the new test? A properly normalized design, and one which
would avoid the need to write any VBA code (see below) would be

ID Test Value
exp_1 1 18
exp_1 2 25
exp_1 3 30
exp_2 1 52
exp_2 2 127
exp_2 3 39
<etc>

Sure, it's 18000 rows - but Access can handle millions of rows, not to
worry.
Experiment 1 has a fold change of 1.67. This is done by taking the
largest value of the three tests (30), and a dividing it by the
smallest of the three (18). (30/18 = 1.66667)

Experiment 2 has a fold change of 3.26. (127/39 = 3.2564)

I need a SELECT statement that would return all (*) information on a
row WHERE the fold change is greater than or equal to (>=) the
predefined number.
Click to expand...

Since your table structure is not normalized, this will require either
a VERY nasty nested IIF, checking all six possibilities twice, or a
VBA function to find the smallest and largest values and divide them.
Air code, untested:

Public Function FoldChange(ParamArray vX() As Variant)
Dim iPos As Integer
Dim iLeast as Integer ' assuming Integer values, use Double if not
Dim iBiggest As Integer
iLeast = 65535 ' use 1.0E308 if values are Doubles
iBiggest = -65535 ' use -1.0E308 if Doubles
For iPos = 0 to UBound(vX())
If vX(iPos) > iBiggest Then iBiggest = vx(iPos)
If vX(iPos) < iLeast Then iLeast = vx(iPos)
Next iPos
FoldChange = iBiggest / iLeast
End Function

You'ld call this in a Query like

FoldChange: FoldChange([Test1], [Test2], [Test3])

and you can apply any criteria you like to it, sort by it, etc.
 
Back
Top