Ordering an alias column in union query

I have a SQL query that utilises a union a few times, and within it I’ve created an alias column called ‘Category’. When I try a simple order by on the alias column it works fine. However the data in my alias column has numbers and they order like 1,10,11 instead of 1,2,3 etc..

When I try changing my order by to order by len, I suddenly get 2 errors… 1 advising that columnn ‘Category’ doesn’t exist and the other stating ‘ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator’. The same applies when I try casting it as an INT

A slimmed down version of the query is as follows…

SELECT 'Category 1' as Category, category1Score as categoryScore
from table1
WHERE lineNumber = 'Reflow 2' and dateOfAudit between '01/10/2019' and 
'10/10/2019'
UNION
SELECT 'Category 2' as Category, category2Score as categoryScore
from table1
WHERE lineNumber = 'Reflow 2' and dateOfAudit between '01/10/2019' and 
'10/10/2019'
UNION
SELECT 'Category 10' as Category, category10Score as categoryScore
from table1
WHERE lineNumber = 'Reflow 2' and dateOfAudit between '01/10/2019' and 
'10/10/2019'

I want it to order by Category 1, Category 2, Category 10 for example.

Answer

You can try below –

select Category,  categoryScore from
(
SELECT 'Category 1' as Category, category1Score as categoryScore,1 as ord
from table1
WHERE lineNumber = 'Reflow 2' and dateOfAudit between '01/10/2019' and 
'10/10/2019'
UNION
SELECT 'Category 2' as Category, category2Score as categoryScore,2
from table1
WHERE lineNumber = 'Reflow 2' and dateOfAudit between '01/10/2019' and 
'10/10/2019'
UNION
SELECT 'Category 10' as Category, category10Score as categoryScore,3
from table1
WHERE lineNumber = 'Reflow 2' and dateOfAudit between '01/10/2019' and 
'10/10/2019'
)A order by ord