Need to query max value between several colums for each row ?

Need to query max value between several colums for each row ?

• FruitBatInShades

  if i correctly understand your problem
  
  your table looks like
  Table_name(
  Id int,
  col1 string,
  col2 string,
  col3 string,
  col4 string,
  col5 string,
  col6 string,
  col7 string
  ETC,,,,)
  
  and for each id you want the greatest string between (col1.....col7)
  
  
  
  Create a new function
  FUNCTION greatest (@string1 char,@string2 char )
  RETURNS char
  AS
  BEGIN
  declare @ret char
  if @string1 > @string2
  set @ret= @string1
  else set @ret= @string2
  return( @ret)
  END
  
  and then
  
  
  select id,
  dbo.greatest(col1,
  dbo.greatest(col2,
  dbo.greatest(col3,
  dbo.greatest(col4,
  dbo.greatest(col5,
  dbo.greatest(col6,col7))))))
  from table_name
  ...
  
  
  

• Madhu.Babu

  I got it too work with the 1'st guy's answer, but yours look intersting too, will there be a performance gain, when you put the logic into a funtion or will it be the same as the previous answer

  Here's how I did it, My problem has just been that I can't work around with the query output I got,

  So I did some work aound that and made a flat file output which I then have to import again into the table "data.MaxVaerdi" is there a smarter way so that I don't have to make 2 jobs pushing and pulling data from a flat txt file

  /Kenneth

  my code ex.

  select dbo.Data_Storage.id_datasource,
  MaxVaerdi = max(case c.n
  when 1 then dbo.Data_Storage.Data00001
  when 2 then dbo.Data_Storage.Data00002
  when 3 then dbo.Data_Storage.Data00003
  when 4 then dbo.Data_Storage.Data00004
  when 5 then dbo.Data_Storage.Data00005
  when 6 then dbo.Data_Storage.Data00006
  end)
  from dbo.Data_Storage
  
  cross join (select 1
  union all select 2
  union all select 3
  union all select 4
  union all select 5
  union all select 6)
  as c(n) group by dbo.Data_Storage.id_datasource
  

  
  

• Aaron C Miller - MO

  You have to use CASE expression to perform MAX or write a scalar UDF to perform the same logic. Inlining the CASE expression in a SELECT statement will provide the best performance. There are other ways using SELECT like below:

   

  select t1.id, max(t1.c)

  from (select id, a from t union all select id, b from t) as t1

  group by t1.id

   

  select t.id, max(case c.n when 1 then t.a when 2 then t.b end)

  from t

  cross join (select 1 union all select 2) as c(n)

  group by t.id

   

  You have to test the performance of the SELECT statements against the CASE expression approach and see.

  
  
  

• vonpato

  Inline expressions will always give the best performance right now. Using scalar UDFs in SELECT list can cause performance problems and it worsens depending on the complexity of the logic in the UDF too. The problem is in calling the UDF for each row that is being returned by the SELECT statement and in some cases depending on the query plan/query the scalar UDF can be calculated before the SELECT list is evaluated.
  
  
• Need to query max value between several colums for each row ?