Sorting in Non-Standard Ways

I have a table called Template containing two fields that I've defined with the following statement:

CREATE TABLE OrderTest   (template int, template_name varchar(20))

The INSERT statements in Listing 1 show the kind of data this table contains. I need to display the result set so that it's sorted alphabetically by the template_name column, but the row that contains the data template_name = 'generic template' needs to come at the end of the list. How can I meet these criteria?

A Many SQL Server users need to sort data in non-standard ways. The ORDER BY clause is more flexible than many T-SQL novices realize and can be the key to non-standard sorting. To demonstrate this flexibility, let's look at a simple answer to this question. The code in Listing 2 shows that you can use the ORDER BY statement to order the result set in two different ways based on the same table column. The ORDER BY clause first sorts by the length of the template_name column, then sorts by the value. The length of the special value generic template is the longest value for the column, so this row will sort last, and you get the result you want.

But there's always more than one way to solve a T-SQL problem. One of the technical editors for this month's column suggested a more elegant solution to the need for non-standard sorting. The solution in Listing 2 assumes that generic template is the only long string that you'll insert into the template_name column. However, what happens if someone inserts these rows into Listing 2

INSERT INTO OrderTest VALUES(6, 'a new template val 1')INSERT INTO OrderTest VALUES(7, 'a new template val 2')

and generic template is the only value that should cause a row to be sorted last? These rows contain values for template_name that are long enough that the LEN() function would cause them to be sorted last; but we don't want those rows to be at the end of the list. In this case, the simple use of a LEN() function doesn't work. The code in Listing 3 shows how you can solve the problem by using a CASE statement to build a powerful and flexible ORDER BY clause. Using CASE in the ORDER BY clause lets you specifically single out the special value generic template and ensure that this is the only row that will be forced to the end of the list.