Wednesday, April 11, 2012

Oracle SQL dynamic order by clause

I never had a dire need for this before today, but it is something I could have used many times in the past to simplify queries and/or use a query instead of a function.


SELECT *
  FROM table alias

ORDER BY DECODE(:bindVariable, 'option1', alias.column0, 'option2', (alias.column1 * alias.column2)) DESC NULLS LAST,
         alias.column3 ASC);

It has been around for at least a decade so, although I don't write a ton of SQL or PL/SQL, I'm surprised I haven't seen it before today.

No comments:

Post a Comment