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).

Add a comment
No HTML tags allowed in comments - thank spammers for this restriction
Author:
E-mail:

Please solve the following math question 11 + 6 is:
19 August 2010 21:18:58

autoradio navigation, car dvd gps navigation, sat navigation stereo, OEM Factory headunit for all car makes Higher quality car electronics from Qualir

comment sent by dress

Loading calendar...

Main Menu
Home

Category
ColdFusion(98)


The CFXML_Blog project
Archives

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