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

sql tsql reportbuilder
confusedKid picture confusedKid · Apr 16, 2012 · Viewed 9.2k times · Source

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!

Answer

Alex K. picture Alex K. · Apr 16, 2012

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More