Append rows to the SELECT query result

Something I stumped over recently when I was working with a list of categories. Simply put, I had to sort the category on the number of questions each have and append category 'Other' at the end.

UNION ALL can help resolve this issue. So All we need to do is-

SELECT category
FROM t_question
WHERE category <> 'other'
GROUP BY category

UNION all

SELECT DISTINCT category
FROM t_question
WHERE category = 'other'


which will give me the list of categories from the table and add category 'other' at the end of the list. But if you need to sort the list (ORDER BY) as I needed to and if you are working on MS SQL Server 2005 then ORDER BY clause does not work with UNION clause.

SO I updated the above query to-

SELECT c.category
FROM (SELECT category, count(*) num
FROM t_question
              WHERE category <> 'other'
              GROUP BY category

              UNION all

              SELECT DISTINCT category, 1
              FROM t_question
              WHERE category = 'other') c 
ORDER BY num DESC


In case you bump into the same issue, now you know how to tweak your query.