SQL Query with Typo not working in MySQL (Order by on calculated field)

I have this query in which one of the column is a calculated one. Every thing is working except it is not ordering the results when I use that calculated column in query. The query is a very large one so I have simplified it below for understanding. Here the calculated column is "remaining"

SELECT t1.id, t1.name, t2.duration - datediff(now(), t1.posting_time) as "remaining"
  FROM table1 t1, ta开发者_StackOverflowble2 t2
 WHERE td.id = t1.timefield
 ORDER BY id, name, remaining DESC

Even if I remove this "remaining" from order by clause or use it with asc or desc, nothing happens and order stays the same.

The only time when 'remaining' alters the sort order is when the id and the name in two rows are the same. Otherwise, it has no effect on the ordering.

You need to fix the typo of 'td.id' to 't2.id'.

You should learn the JOIN notation too:

SELECT t1.id, t1.name, t2.duration - datediff(now(), t1.posting_time) as "remaining"
  FROM table1 t1
  JOIN table2 t2 ON t2.id = t1.timefield
 ORDER BY id, name, remaining DESC

use a trick:

SELECT *
FROM (
  SELECT
    t1.id, 
    t1.name, 
    t2.duration - datediff(now() , t1.posting_time) as "remaining"
  FROM table1 t1, table2 t2
  WHERE t2.id = t1.timefield
  ) AS i
ORDER BY id, name, remaining DESC