Round to the nearest odd integer in SQL

Question

I've found myself somewhat improbably* needing to round to the nearest ODD integer in SQL. There's a nice solution for how to round to the nearest N (2,5,10,etc) here, but nothing explicitly on odd numbers. Using Oracle 11gR2, if there are solutions particular to Oracle out there.

*Need to join my data to tables stripped from this study. The authors used a consistent bin width of 2...but sometimes it's even, and others it's odd.

Answer 1

You could do something like this:

DECLARE
  n FLOAT;
BEGIN  
   n := 195.8;
   SELECT 
      CASE
         WHEN mod(FLOOR(n),2) = 0 THEN FLOOR(n)+1
         ELSE FLOOR(n)
      END NUM
      INTO n
   FROM DUAL;
   dbms_output.put_line(to_char(n));
   END;
/

Sometimes straightfoward is best, as people who come along after you will understand what's going on.

Answer 2

I don't think you need a case statement, this should do it:

SELECT 
   ROUND((11.9-1)/2,0)*2+1
FROM DUAL