Complex ordering in SQL Query: possibly using CASE WHEN

Question

I have a complex prioritisation algorithm that I want to write in SQL to when return prioritised pages of data.

Some of these are prioritised according to fixed values, other are ordered by variable values. i.e.

// Initial sort on ATTR1 (value1/value2 is higher than value3)
if ATTR1 = value1 or value2
  then orderBy creationDate, then modifiedDate, then author
else if ATTR1 = value3
  then 
    // if ATTR1 = value3, do secondary sort on ATTR2 (value4 is higher than value5)
    if ATTR2 = value4
       then orderBy oldPriority, then modifiedDate, then creationDate, then author
    if ATTR2 = value5
       then orderBy creationDate, then modifiedDate

I have had a look at SQL CASE WHEN, but not sure how to make that work with the second level of attribute comparison.

Is CASE WHEN a suitable tool for achieving this? Does anyone have any tips on dealing with the additional levels of complexity?

Thank you in advance!

Answer 1

Something like this should do it:

ORDER BY attr1,
CASE WHEN ATTR1 IN (value1, value2)
       THEN TO_CHAR(creationDate,'YYYYMMDDHH24MISS') 
            || to_char(modifiedDate,'YYYYMMDDHH24MISS') || author
     WHEN ATTR1 = value3
       THEN attr2 || CASE WHEN ATTR2 = value4
                            THEN to_char(oldPriority,'99999')
                                 || to_char(modifiedDate,'YYYYMMDDHH24MISS')
                                 || to_char(creationDate,'YYYYMMDDHH24MISS') 
                                 || author
                          WHEN ATTR2 = value5
                            THEN to_char(creationDate,'YYYYMMDDHH24MISS')
                                 || to_char(modifiedDate,'YYYYMMDDHH24MISS')
                          END
     END

I assumed that oldPriority was a NUMBER column, which may not be the case. The point is to convert everything to strings that order correctly, using appropriate format masks.

Answer 2

Making a couple small assumptions, it seems this should work:

order by
   case ATTR1
      when value1 then
         1
      when value2 then
         2
      when value3 then
         3
   end,
      when ATTR1 in (value1,value2) 
           or (ATTR1 = value3 and ATTR2 = value5) then
         creationDate
      else
         null
      end,
   case 
      when ATTR1 = value3 and ATTR2 = value4 then
         oldPriority
      else
         null
      end,
   modifiedDate,
   case
      when not (ATTR1 = value3 and ATTR2 = value5) then
          author
      else null
      end