TSQL How to order parameter values with “ALL”?

I am developing an SSRS 2008 report and am trying to get one of my stored procs to output "-All-" unioned with the other possible values for this parameter, where "-All-" appears as the first value. Instead, "-All-" is sorted in alphabetical order so that "Adams" is listed first. How do I make "-All-" come first? (Note that this parameter is a uniqueidentifier, so I cannot make "-All-" = -1. Here is my T-SQL code right now:

Select NULL As [client_id], NULL AS [id_no], '-All-' As [full_name], '-All-' As [id_and_name]
UNION ALL
Select Distinct [people_id] AS [client_id], [id_no], [full_name], [full_name] + ' : ' + [id_no] AS [id_and_name]
From [evolv_cs].[dbo].[service_track_current_view] With (NoLock) 
Order By [full_name]

When I tried this code:

Select NULL As [client_id], NULL AS [id_no], '-All-' As [full_name], '-All-' As [id_and_name]
UNION ALL
Select Distinct [people_id] AS [client_id], [id_no], [full_name], [full_name] + ' : ' + [id_no] AS [id_and_name]
From [evolv_cs].[dbo].[service_track_current_view] With (NoLock) 
Order By 
CASE [full_name] WHEN '-All-' THEN 0 ELSE 1 END,

[full_name]

I got this error:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'full_name'.
Msg 104, Level 16, State 1, Line 6
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Answers


If it's possible for you to add a column, then you could add 0 AS [sort_value] to the first part of the union and 1 AS [sort_value] to the second part of the union.

Then simply Order By [sort_value],[full_name]


Update Turns out we can't use the column names that we've assigned within the query, from the ORDER BY clause. This updated code should work, however:

SELECT * FROM (
    Select NULL As [client_id], NULL AS [id_no], '-All-' As [full_name], '-All-' As [id_and_name]
    UNION ALL
    Select Distinct [people_id] AS [client_id], [id_no], [full_name], [full_name] + ' : ' + [id_no] AS [id_and_name]
    From [evolv_cs].[dbo].[service_track_current_view] With (NoLock) 
)tbl
ORDER BY
    CASE [full_name] WHEN '-All-' THEN 0 ELSE 1 END,
    [full_name]

That is, you first order by whether or not the value is -All-, and within those two groups, you order by the actual value itself.


Need Your Help

Wireshark not displaying GET or POST data

http networking get wireshark packet-sniffers

I'm a student and I'm taking my first networking class. I'm working on an assignment designed to get me used to using Wireshark and understanding packet transfers. Part of the assignment is to coll...

Is onBootstrap() necessary in a Zend 2 module?

php zend-framework zend-framework2

In the Zend 2 skeleton application, the Application module.php contains a onBootstrap() function:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.