XML FeedsHere is something interesting to think about
Create this table and insert these 2 rows
tsql
- create table TableName(id int, name varchar(50))
- insert TableName values(1,'bla')
- insert TableName values(2,'bla2')
Now if you try to do something like this
tsql
- SELECT TOP 1 ID,Name
- FROM TableName
- ORDER BY Name
- UNION ALL
- SELECT 0,''
You will get the following error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'UNION'.
Interestingly, this same code will work if you use it in the following matter
tsql
- SELECT TOP 1 *
- FROM (
- SELECT TOP 1 ID,Name
- FROM TableName
- ORDER BY Name
- UNION ALL
- SELECT 0,''
- ) X
- ORDER BY ID DESC
This same code also works with Common Table Expressions
tsql
- WITH query AS (
- SELECT TOP 1 ID,Name
- FROM TableName
- ORDER BY Name
- UNION ALL
- SELECT 0,'')
- SELECT TOP 1
- x.id,
- x.name
- FROM query x
- ORDER BY x.id DESC
My assumption is that the optimizer disregards any order by in the subquery. Still I find this interesting because usually a query will work, you stick it inside another piece of code and it will complain about something
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
I was just working on something similar today and your original snippet would work if changed to this:
John, I know that what you have works. What I am trying to figure out is why something that doesn't (and shouldn't work) does work in a CTE or subquery
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.