Can't reference a column to order by when outputting to XML

Transact-SQL
1

Afternoon all,

I have a little problem here I can't seem to work out. Small query out putting to XML as the results are insterted into a table on an email, problem is I can't seem to work out how to 'order by' the calculated field... Any ideas greatly recieved. Thanks Dave.

SET @xml = CAST((	
	SELECT
		Emp_Name AS 'td','',
		Emp_Job AS 'td','', 
		CAST(dbo.CalcDistance(G1.Lon, G1.Lat) AS DECIMAL(3,2)) AS 'td','',
		Emp_Transport AS 'td',''
	FROM Employees
	FULL OUTER JOIN Geolocation G1
		ON G1.Postcode = REPLACE (Emp_Postcode, RIGHT(Teachers.T_Postcode, 3), '')
    WHERE Emp_Status = 'Live'
    --ORDER BY Distance
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
2

Hi

Please provide sample data to work with
Solution providing becomes easier

drop table #data
go

create table #data
(
id int,
name varchar(100)
)
go

insert into #data select 1,'harish'
insert into #data select 2,'pam'
insert into #data select 3,'Ravi'
go

3

Easiest is to repeat the entire expression in the ORDER BY:

	SELECT
		Emp_Name AS 'td','',
		Emp_Job AS 'td','', 
		CAST(dbo.CalcDistance(G1.Lon, G1.Lat) AS DECIMAL(3,2)) AS 'td','',
		Emp_Transport AS 'td',''
	FROM Employees
	FULL OUTER JOIN Geolocation G1
		ON G1.Postcode = REPLACE (Emp_Postcode, RIGHT(Teachers.T_Postcode, 3), '')
    WHERE Emp_Status = 'Live'
    **ORDER BY CAST(dbo.CalcDistance(G1.Lon, G1.Lat) AS DECIMAL(3,2))**
4

Cross Apply the calculation, then you can reference it both in the results and the Order By clause:

SELECT
	Emp_Name AS 'td','',
	Emp_Job AS 'td','', 
	X.Distance AS 'td','',
	Emp_Transport AS 'td',''
FROM Employees
FULL OUTER JOIN Geolocation G1
	ON G1.Postcode = REPLACE (Emp_Postcode, RIGHT(Teachers.T_Postcode, 3), '')
CROSS APPLY
(
	Select CAST(dbo.CalcDistance(G1.Lon, G1.Lat) AS DECIMAL(3,2)) As Distance
) X
WHERE Emp_Status = 'Live'
ORDER BY X.Distance
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
5

Great stuff, both solutions work perfectly, thank you!!