How to keep a specific row as the first result of a query (T-SQL)?

reportbuilder, sql, t-sql

Solution

One way;

SELECT Name FROM (
    SELECT 'All'       as Name
    UNION 
    SELECT DISTINCT    Manager
    FROM               IS_Projects
) T
ORDER BY CASE Name WHEN 'All' THEN 0 ELSE 1 END, Name

Problem

I'm writing a SQL query to get a list of parameters for a report in Report Builder 3.0. I needed to add an extra row with the value 'All' to the results like this: ``` SELECT 'All' UNION SELECT DISTINCT Manager FROM IS_Projects ``` This works fine, but the query returns the rows to me sorted in alphabetical order, where I actually want 'All' to appear at the top at all times (ie. come back as the first row). The rest of the results can be sorted alphabetically. I've seen suggestions on adding a sort-order column to the table, but I'm pretty new to SQL, and don't know how to do this. Thanks for any suggestions!

Original source