Select the smallest value in a row of columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Select the smallest value in a row of columns

  1. #1
    Join Date
    Feb 2004
    Location
    Lubbock, Texas
    Posts
    3

    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

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    How about

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

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I think you want:
    LEAST(col_1, col_2, col_3)

  4. #4
    Join Date
    Feb 2004
    Location
    Lubbock, Texas
    Posts
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    Lubbock, Texas
    Posts
    3
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width