
Select the smallest value in a row of columns
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

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

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

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

select * from my_table
where
greatest(test_1, test_2, test_3)/least(test_1, test_2, test_3) > :your_predefined_number;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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

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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
