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