Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    12

    Question Unanswered: Greatest problem

    I'm having a problem with getting the greatest of 2 different values.

    SELECT
    Identification_here,
    Score 1 = max(CASE when Test_ID = '0102' then Test_Score else null end),
    Score 2 = max(CASE when Test_ID = '0105' then Test_Score else null end),
    Score 3 = max(CASE when Test_ID = '0109' then Test_Score else null end),
    Score 4 = max(CASE when Test_ID = '0112' then Test_Score else null end),
    Score 5 = max(CASE when Test_ID = '0101' then Test_Score else null end),
    Score 6 = max(CASE when Test_ID = '0502' then Test_Score else null end),
    Score 7 = max(CASE when Test_ID = '0505' then Test_Score else null end)

    FROM
    #table_name

    Okay the above works in our prodcution. Some info has been changed for demonstration purposes but thats essentally how it is laid out.
    But i have been approached as to get the greatest of '0502' and '0202'
    and same for '0505' and '0203'
    i have google'd this and tried

    Score 7 = max(CASE when Test_ID = (GREATEST('0505','0203')) then Test_Score else null end)

    But it error's out. Any suggestions? Do i need to write a case within a case or can i get the Greatest function to work?
    Last edited by eggone38; 06-07-06 at 16:20.

  2. #2
    Join Date
    Aug 2004
    Posts
    42
    If you want the maximum score from among the test IDs 0505 and 0203 then try

    Score 7 = max(CASE when Test_ID in ('0505','0203') then Test_Score else null end)

Posting Permissions

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