Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Wink Unanswered: comparison between columns

    How to compare the values between columns? I know there's a function "Greatest" in Oracle, do MS SQL have function similar to that?

    And I want to create a view from a table, and one column 'll be selected from the 1 of the 4 related columns in table, say, If ColA is not null then the value is from ColA, if not , the value of the column in the view 'll be from ColB. How can i do this? Do I have to use store procedure?
    ( I know that's the result of poor normalization, but ... I'm not allowed to change th schema)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql/server apparently does not have anything as useful as the GREATEST function

    unless someone else has a better idea, you'll have to use the CASE structure to build the equivalent --

    select
    (case
    when a>=b and a>=c and a>=d then a
    when b>=a and b>=c and b>=d then b
    when c>=a and c>=b and c>=d then c
    when d>=a and d>=b and d>=c then d
    else a
    end) as greatest

    as for your other question, that's easy --

    select coalesce(colA, colB) as ifAnullthenB

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What happens if you have Nulls?

    Code:
    --------------------------------------------------------------------------------------------------------------------------------
    create table #Tmp(a int, b int, c int, d int)

    insert into #Tmp values(1, Null, Null, Null)
    insert into #Tmp values(Null, 2, Null, Null)
    insert into #Tmp values(Null, Null, 3, Null)
    insert into #Tmp values(Null, Null, Null, 4)

    insert into #Tmp values(5, 4, 3, 2)
    insert into #Tmp values(3, 6, 4, 5)
    insert into #Tmp values(4, 5, 7, 6)
    insert into #Tmp values(5, 6, 7, 8)

    select case
    when a >= b and a >= c and a >= d then a
    when b >= a and b >= c and b >= d then b
    when c >= a and c >= b and c >= d then c
    when d >= a and d >= b and d >= c then d
    else a
    end as greatest
    from #Tmp

    select case
    when (b >= a or a is null) and (b >= c or c is null) and (b >= d or d is null) then b
    when (c >= a or a is null) and (c >= b or b is null) and (c >= d or d is null) then c
    when (d >= a or a is null) and (d >= b or b is null) and (d >= c or c is null) then d
    else a
    end as greatest
    from #Tmp
    --------------------------------------------------------------------------------------------------------------------------------

    Results:
    --------------------------------------------------------------------------------------------------------------------------------
    greatest
    -----------
    1
    NULL
    NULL
    NULL
    5
    6
    7
    8

    greatest
    -----------
    1
    2
    3
    4
    5
    6
    7
    8
    --------------------------------------------------------------------------------------------------------------------------------

    Which is correct?
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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