MySQLism: avoid nested ORDER BY, use nested queries

SQL-99 allows nested queries, but forbids nested ORDER BY. The reason is that nested queries improve the ability of the parser to understand the query.

Nested ORDER BY clauses works under MySQL but fail under PostgreSQL:

(SELECT thread 
FROM comments 
WHERE nid = 49805 AND status = 0 
ORDER BY timestamp DESC LIMIT 326) 
ORDER BY thread DESC LIMIT 1

The correct SQL query to execute under both environments should be:

SELECT thread FROM 
(SELECT thread 
FROM comments WHERE nid =49805  AND status = 0 
ORDER BY timestamp DESC LIMIT 326) 
AS thread ORDER BY thread DESC LIMIT 1

There is no speed issue in replacing nested ORDER BY with nested queries. In MySQL and PostgreSQL, the parser will rewrite the queries to execute as nested queries.

References:
http://drupal.org/node/396388