>Ignorance is yours because your example doesn't prove any points you made

Nice, running away withno constructive arguments whatsoever. I'd expected better from you.
I see you're not interested in being proved anything. Have a nice day.



>
>>
>>That is a statement from ignorance.
>>Lets see, the example given in this thread.
>>
>>

WITH totals (code, qty) AS 
>>(SELECT code, SUM(qty)
>>    FROM #Table1
>>    GROUP BY code)
>>SELECT t1.code, tn.Names, tn.Country, t1.qty
>>FROM totals t1
>>    CROSS APPLY (SELECT TOP (1) names, country
>>    FROM #Table1 t2
>>    WHERE t1.code = t2.code) tn(Names, Country)
>>ORDER BY t1.code;
>>

>>
>>Vs
>>
>>

>>select x.code, y.Names, y.country, x.Qty
>>	FROM 
>>		(SELECT code, sum(Qty) as Qty
>>			FROM #Table1 T1
>>			GROUP BY code
>>		) AS x
>>	CROSS APPLY (SELECT TOP 1 names, country FROM #Table1 T2 WHERE x.Code = T2.code) as y
>>

>>
>>You really want to argue that the fist one is better readable than the latter. If you really think that, then I agree there is no point in arguing. I'd never use a CTE the for that. Its overkill and inappropriate use of CTEs and to me its a very bad smell of SQL skills.
>>
>>The problem with the CTE in the example above
>>1. Its lenght: There is more to read.
>>2, Its a lot less readable for programmers not comfortable with CTE's. This is a bigger problem that you'd think. The UT is littered with examples of that.
>>3. Its somewhat more difficult to spot where the actual query begins, as it is not at the beginning of the statement
>>4. When not using naming conventions (like the example above) you're not sure what in the SELECT statement is a CTE or an actual table. You'll have to read back to the top.
>>5. When reading the actual query, in your you'll mind have to substitute the CTE into the query to really understand what is going on. This is esspecially a problem in troubleshooting issues in execution and performance.
>>
>>Point 5 would be a strenght if the CTE is a strong abstraction where its definition is universally known. Then you're abstracting away the complexity. However at that point it might be wise to think about an inline function as you might want to use this for other queries as well.
>>
>>Again, my opinion.
>>
>>
>>
>>>>
>>>>And I srongly disagree with that. Most likely you like it from an abstraction point of view. I'd argue that in many cases, if the abstraction is that clear and strong it most likely deserves its ow inline function. In most practical cases the abstraction is complex and flawed and hinders the readbility and makes trouble shooting more difficult in the first place.
>>>>
>>>>In the case of the original topic, I'd never, ever use a CTE. its overkill and does not result in shorter and more readable code.
>>>>
>>>>Walter,