Maximum value from multiple columns of multiple tables.

• Vijay Patel

  SSC Enthusiast

  Points: 164

  More actions

  January 6, 2005 at 11:25 am

  #61215

  Hi

  I have two table T1 (columns C10 & C11) and T2 (columns C20 & C21).

  /*-----------------------------

  SELECT   * FROM T1

  SELECT   * FROM T2

  -----------------------------*/

  C10         C11        

  ----------- -----------

  10          20

  (1 row(s) affected)

  C20         C21        

  ----------- -----------

  11          19

  (1 row(s) affected)

  Can I have single sql where I can find the max value from C10, C20 and C11,C21.

  So I expect to see 11 and 20 as an output. (not 10 and 19)

  Thanks.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  January 6, 2005 at 12:03 pm

  #536260

  You can try this...  I see no relationship between these tables; this is a straight select based on max value from a column. 

   

  SELECT 10 AS 'C10', 20 AS 'C11' INTO #T1

  SELECT 11 AS 'C20', 19 AS 'C21' INTO #T2

  SELECT CASE

    WHEN MAX( C10) > MAX( C20)

    THEN MAX( C10)

    ELSE MAX( C20)

   END AS 'C10_20',

   CASE

    WHEN MAX( C11) > MAX( C21)

    THEN MAX( C11)

    ELSE MAX( C21)

   END AS 'C11_21'

  FROM #T1, #T2

  DROP TABLE #T1

  DROP TABLE #T2

  I wasn't born stupid - I had to study.

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 6, 2005 at 12:26 pm

  #536262

  Select Max(C10) MaxC1, Max(C11) MaxC2

  from

  ( select C10 , C11 from T1

    UNION ALL

    select C20, C21 From T2

  ) Q

  

  
  * Noel

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  January 6, 2005 at 12:42 pm

  #536268

  Thanks noeld.  I was trying this with a UNION ALL, but I could not get the syntax down correctly; forgot to allias the UNION

  . 

  I wasn't born stupid - I had to study.