Making subquery workable

Extend the article: SQL - Interesting Queries as a series of articles:

• Interesting Query (1) --- Find Second Highest Salary
• Interesting Query (2) --- Whtch Manager has no Employee with
• Interesting Query (3) --- Get Rolling Average 
  • Making subquery workable --- this article

When working with SubQuery, the behavior or requirement might not be the save as ordinary query.

This query is working:

However, as a subquery it does not working any more:

The reason is the subquery needs an alias: when we added it, the original error is gone, but a new one is coming: 

This error is due to the missing column name. That is good for ordinary query, but not for subquery:

adding column name, it is working now:

Adding "ORDER BY", this does not work, with errors:

• The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

As suggested, adding TOP it is working

 

References:

• SQL GROUP BY Statement (w3schools.com)
• SQL | Sub queries in From Clause - GeeksforGeeks
• Can we use a group by in a subquery? - Quora
• The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. - Microsoft Q&A