Special Function of ORDER BY Clause in T-SQL

Compare to MS Access Jet Engine SQL, T-SQL's ORDER BY clause is flexible, which can point either the column name or alias of the column. Which special function can be used for special purpose. Here is any example. Let us say, you need a data set in order:

SELECT DISTINCT Name
FROM Tab
ORDER BY Name

Then you need to add a record called "Unknown" at the top:

SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab

There is problem, you won't able to add ORDER BY clause in T-SQL because you used UNION. Following is next step:

SELECT Name
FROM
( SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

You won't be able to get the right result, because "Unknown" is not on the top. Solution is:

SELECT CASE WHEN Name = '0000' THEN 'Unknown' ELSE Name END AS New_Name
FROM
( SELECT '0000' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

Why use '0000'? Because it would be on the top after sort. In this case, if you use

ORDER BY New_Name

it won't work.


http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html

Error Message: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position...

This SQL Server full error message is: "The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

It happens when a bind variable is placed in ORDER BY clause. For dynamic SQL, the solution is Database Engine Stored Procedures sp_executesql. For example:

Your failed original statement:
SET @stddev = SELECT STDEVP(@EachColumn)
FROM Temp_IQM_Input
ORDER BY @EachColumn

The solution:
DECLARE @stddevString NVARCHAR(1000)
SET @stddevString = ' SELECT @out = STDEVP(@para) '
+ ' FROM Temp_IQM_Input '
+ ' ORDER BY @para '
DECLARE @stddev INT
EXECUTE sp_executesql @stddevString
, N'@para VARCHAR(32), @out INT OUTPUT'
, @para = @EachColumn
, @out = @stddev OUTPUT
;

For sp_executesql statement, the 1st line is the sql query in string, 2nd line to define the bind variables, if the parameter used for output, it needs to be stated. The rest lines are for variables assignments.

It is painful because there are too many WIP parameters; but it is the way.

Quick Sorting Method in SQL Query for Disorderly Sorting Criterion

ORDER BY can be used for orderly sorting criterion only. For disorderly sorting criterion, such as Car, Ship, Airplane, ORDER BY could not accomplish the task. Here is a quick method to use ORDER BY as usually.

Step One: Add order number in front of sorting field by using concatenation function.

Step Two: Using ORDER BY as usually.
Step Three: Using substring function to cut the first character.

Example in T-SQL:

SELECT SUBSTRING(newSort, 2, 8)
, Other_Fields
FROM
(
SELECT CASE WHEN Sort_Field = ‘Car’ THEN ‘1Car’
WHEN Sort_Field = ‘Ship’ THEN ‘2Ship’
WHEN Sort_Field = ‘Airplane’ THEN ‘3Airplane’ END AS newSort
, Other_Fields
FROM Tab
) AS innerQuery
ORDER BY newSort

This method can be used in T-SQL, PL/SQL, etc. For T-SQL, please pay special notice to this: ORDER BY cannot be used for any inner query, it must be placed at outer most query. The solution to overcome this weakness is to either abandon this method or save inner query result to temporary table.