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!
To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.
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
This is one way:
SELECT Manager
FROM (SELECT 'All' Manager, 1 Order
UNION ALL
SELECT DISTINCT Manager, 2 Order
FROM IS_Projects) A
ORDER BY Order, Manager
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With