11 April 2007 5:49 PM
Using CASE in SQL to create dynamic ORDER BY

It is a frequent task when creating a report - sort by every column. Many CF developers approach this by creating main query body and then use CFIF to create dynamic ORDER BY for each sort column. This solution works but what if you wanted to do the same thing in SQL? What if your query was very complicated and you wanted to speed things up and place all your SQL into a stored procedure?

What you need is a way to change the way ORDER BY orders things dynamically, your sort condition must depend on a value of a variable. Here is a way to do it - example is the best way to go:

ORDER BY CASE WHEN @sortColumn = 'columnName1' THEN columnName1 END DESC, CASE WHEN @sortColumn = 'columnName2' THEN columnName2 END DESC, CASE WHEN @sortColumn = 'columnName3' THEN columnName3 END DESC

You can modify above construct to make it work with computed columns and with DESC ASC selection (you want users to click once on a column and sort ASC, clicking second time sorts DESC).

As a side note, remember that sorting may work a bit differently depending on your locale (i.e. the language selection made).

Loading calendar...

Main Menu


The CFXML_Blog project

View or subscribe to the links below to access the website content in XML RSS 2.0 format. RSS 2.0
[ short long ] ColdFusion

Which web development language do you use?

Poll answers
View Results

Valid RSS!
Valid CSS!
Valid XHTML 1.0!
Powered by CFXML_Blog
Powered by ColdFusion MX