10

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!

confusedKid
  • 3,231
  • 6
  • 30
  • 49
  • This lives on the report itself, so I'm not actually populating the table this way. Thanks for the tips though! – confusedKid Apr 16 '12 at 15:10
  • Ah...I deleted my comment when I re-read the question and realised it was for a report! Also saw the neat answers below, and figured my comment was no-longer necessary... – Jonathan Sayce Apr 16 '12 at 20:53

2 Answers2

23

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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Awesome - didn't know you could put case statements in the ORDER BY clause. This just opened doors. Is it fairly performant? – crush May 10 '16 at 02:52
  • Personally I would not expect a trivial case like the above to have a measurable effect. – Alex K. May 10 '16 at 10:38
3

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
Lost in Alabama
  • 1,653
  • 10
  • 16
Lamak
  • 69,480
  • 12
  • 108
  • 116