Subquery in ORDER BY

Subquery in ORDER BY is a SQL statement in the Subqueries category. A subquery in ORDER BY sorts by a computed value from another query. The syntax is SELECT * FROM employees ORDER BY (SELECT MAX(salary) FROM employees) - salary;. It returns subquery-sorted result. A typical example: SELECT name, department, salary FROM employees e ORDER BY ( SELECT AVG(salary) FROM employees WHERE department = e.department ) - salary DESC; -- Order by difference from department average -- Employees far below their dept average appear first -- More practical: SELECT * FROM products ORDER BY (SELECT AVG(rating) FROM reviews WHERE product_id = products.id) DESC; A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query.