CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 3095

MySQL IF inside a SWITCH CASE statement

I'm trying to put a couple of if statements inside of a CASE WHERE in a select query to control the order by. This is a simplified version that is still giving me a syntax error.

SELECT stt_stid, st_stid, st_prid, st_first, st_last, YEAR(st_graduation)
  FROM sttlink
  INNER JOIN student ON stt_stid = st_stid
  WHERE stt_prid = 3920103
  GROUP BY stt_stid
  ORDER BY
    CASE 
      WHEN (MONTH(now())>='9')
        THEN    
          if(YEAR(st_graduation) = YEAR(now()), 0, 1),
          st_graduation
      WHEN (MONTH(now())<'9')
        THEN
          if(YEAR(st_graduation) >= YEAR(now()), 1, 0),
          st_graduation
      END;

And the error message (Navicat) is:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
      st_graduation
  WHEN (MONTH(now())<'9')
    THEN
    ' at line 13

Here's more stuff (thanks for looking so far!). This is my original working version. The issue is that I need to have two different order by clauses - one when the month is Sept-Dec, and the other for Jan-August.

ORDER BY if(YEAR(st_graduation)>=YEAR(now()), 0, 1),
        if(YEAR(st_graduation)>=YEAR(now()), 10000, datediff(now(),st_graduation)),
        st_graduation

The above works nicely if it's Jan-August but I need a slightly different one for months Sept-Dec.

Rahul's answer is very close. Now I'm down to a 2nd case inside the statement:

ORDER BY 
CASE 
  WHEN (MONTH(now()) >= '9')
    THEN    
      CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 0 ELSE 1 END,
      CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 10000 ELSE (datediff(now(),st_graduation)) END
  WHEN (MONTH(now()) < '9')
    THEN
      CASE WHEN YEAR(st_graduation) >= YEAR(now()) THEN 1 ELSE 0 END,
      CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 10000 ELSE (datediff(now(),st_graduation)) END
  END, st_graduation;

Should I create a 2nd separate "main" case/when - same when clauses but with just the 2nd case statements?

Upvotes: 2

Views: 2147

Answers (2)

Rahul
Rahul

Reputation: 77906

I think you meant to do like below using a Nested CASE expression

ORDER BY
    CASE 
      WHEN MONTH(now()) >= '9' THEN    
          CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 0 ELSE 1 END

So in your case, the ORDER BY clause should look like

  ORDER BY
    CASE 
      WHEN (MONTH(now()) >= '9')
        THEN    
          CASE WHEN YEAR(st_graduation) = YEAR(now()) THEN 0 ELSE 1 END          
      WHEN (MONTH(now()) < '9')
        THEN
          CASE WHEN YEAR(st_graduation) >= YEAR(now()) THEN 1 ELSE 0 END
      END, st_graduation;

Upvotes: 3

AlexGreg
AlexGreg

Reputation: 838

what about 

SELECT stt_stid, st_stid, st_prid, st_first, st_last, YEAR(st_graduation)
  FROM sttlink
  INNER JOIN student ON stt_stid = st_stid
  WHERE stt_prid = 3920103
  GROUP BY stt_stid
  ORDER BY
    CASE 
      WHEN (MONTH(now())>='9' and YEAR(st_graduation) = YEAR(now()))
        THEN 
          st_graduation
      WHEN (MONTH(now())<'9' and YEAR(st_graduation) >= YEAR(now()))
        THEN
          st_graduation
      END;

Upvotes: 1

Related Questions