How to order a SQL statemnet according to alias name

I have the next SQL statement:

SELECT
p.ID,
p.TheName0, 

(SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att 
 INNER JOIN tbl_students st 
 ON st.ID = att.StudentID
 INNER JOIN tbl_groups_classes cls
 ON cls.ID = att.ClassID

 WHERE st.ParentID = p.ID 
and cls.TheDate BETWEEN @Date1 and @Date2 
and att.TheStatus <> 'absent'
) as CurrMost,

(SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att 
 INNER JOIN tbl_students st 
 ON st.ID = att.StudentID
 INNER JOIN tbl_groups_classes cls
 ON cls.ID = att.ClassID

 WHERE st.ParentID = p.ID and cls.TheDate< @Date1 and att.TheStatus <> 'absent'
) as PrevMost,

(SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
AND pay.TheDate BETWEEN @Date1 and @Date2
) as CurrMadf,

(SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
AND pay.TheDate < @Date1
) as PrevMadf,

(SELECT CurrMost + PrevMost) as AllMost,
(SELECT CurrMadf + PrevMadf) as AllMadf,
(SELECT AllMost - AllMadf) AS FinalTotal 

from tbl_parents p

I want to order it by FinalTotal, I tried to put :

from tbl_parents p order by FinalTotal

but it doesn’t be affected.

how I can sort it? and please note that I tried many solutions on the internet but without result. thanks advanced

Answer

The fail-safe solution is to place the whole query as a table expression to produce the column name, and then sorting is trivial.

For example:

select *
from (
  -- your query here
) x
order by FinalTotal

Advertisement

Answer