Search code examples
sqlsybase

SQL: Calculate the interval a value falls into

I have a column like this:

column1   
      4
     12
     23
      6
     14
     35

I want to create another column next to it that will show the range that the value from column1 falls into, like this:

column1        column2  
      4            0-5
     23          21-25 
      6           6-10
     14          11-15
     33          31-35

It seems to be a simple ranking task, but I'm having trouble making it work. I am pretty new to SQL and probably am missing something basic. So maybe just point a direction for me to go from where I am now, because I am lost.

So far I have looked into using CASE (with no success) and RANK(). The latter doesn't work for me at all, as in it's not even highlighted as a keyword (I am using Sybase Interactive SQL v12.0.1). Although it looks to me that RANK() is not the way to go even if it worked, since I need my values ranked in precise ranges (0 to 35 with step 5).

Thank you all in advance!

Solution

  • Assuming you want to group values inside 0-5 (special case), 6-10, 11-15 and so on, just use the following query (written for SQL Server but illustrates the idea):

    SELECT num, CONCAT(
    CASE WHEN num <= 5 THEN 0 ELSE FLOOR((num - 1) / 5) * 5 + 1 END,
    '-',
    CASE WHEN num <= 5 THEN 5 ELSE FLOOR((num - 1) / 5) * 5 + 5 END
)
FROM ...

    Tests on DB Fiddle

    Replace FLOOR(x / y) with integer division operator if available.
    Replace CONCAT with appropriate string concatenation operator or function.