Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: Query: Column having the largest value

    There are 10 columns in a table all with number data type.
    How to select the column having the largest,smallest value?
    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select greatest(col1,col2,col3,...,col10) as largest
    , least(col1,col2,col3,...,col10) as smallest
    from mytable
    ...;

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Lovely solution, Tony!

    My thoughts were different - to me, it seemed that the question was (in my words) "select COLUMN NAME (of 10 columns) which contains the largest value of all". Therefore, not to select actual VALUE, but COLUMN NAME.

    If we modify your query a little bit:

    SELECT MAX(GREATEST(col1, col2, col3, ...)) FROM table_name;

    we will select the largest number value in all columns involved in the query.

    But, is it possible to select COLUMN NAME that contains the value we got using the above query? Using 'user_tab_columns' or something?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could do something like this:
    Code:
    with v as
    ( select 'COL1' as name, col1 as value from mytable
      union all
      select 'COL2' as name, col2 as value from mytable
      ...
      union all
      select 'COL10' as name, col10 as value from mytable
    )
    select name, value from v
    where value = (select max(value) from v);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •