Extend the article: SQL - Interesting Queries as a series of articles:
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: