Select the smallest value in a row of columns

[crosseyed]

I need a SELECT statement that will return the smallest value per row in a list of columns. For instance:

example table:
---------------------------------
ID | col_1 | col_2 | col_3
---------------------------------
row_1 | 18 | 25 | 30
row_2 | 52 | 73 | 39
row_3 | 24 | 12 | 41

I need a statement that will return me the values 18, 39, 12 in the result set. Can some someone please help.

Thanks,

-2 Peter 3:18

[Highlander]

How about

SELECT MIN(COL_1), MIN(COL_2), MIN(COL_3)
FROM MY_TABLE.

[DaPi]

I think you want:
LEAST(col_1, col_2, col_3)

[crosseyed]

Let me restate the problem in it's entirity. I have a table of over 6000 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:

---------------------------------
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 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.


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 except exp_8.


This is what I need the query to do. Is anyone up for the challenge?

With much thanks,
-Crosseyed

[jmodic]

select * from my_table
where
greatest(test_1, test_2, test_3)/least(test_1, test_2, test_3) > :your_predefined_number;

[crosseyed]

That would work, however I can't use LEAST and GREATEST. This is a web application using ASP to query an Access database. I get the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'LEAST' in expression.

What else can I do?
-Crosseyed

[jmodic]

Originally posted by crosseyed
What else can I do?

Maybe posting into an appropriate forum, i.e. the one that deals with MS Access and not with Oracle RDBMS?