>Really?? I do not know what a CTE (common table extpression) is what I can do with them?? Im very curious how you draw that conclusion. I've writen many, many CTEs, mainly to solve hierarchial problems which by nature are the most complex form of CTE's.
>
>It sounds like you fail to see that CTE's are missing the property to use it as input for another query. Yes, you can rewrite (move the CTE defniation out) it to do that, but that IS NOT the point here. the point is that without CTE you can use a SELECT statement (as its written) for use in another query. Whether that is in a join, EXISTS() or IN(), etc. With CTE you'll have to reorder the components to make that work which really is a problem when dynamically composing SELECT statement.
>
>I challenge you to provide an example where a CTE is a demonstratable better solution than subqueries for a problem where
>1. The query is not hierarchical (self referencing) in nature
>2. The query does not reference the CTE more than once.
>
>>Let's agree to disagree.

I reasoned that from the sample syntaxes you have given, trying to use a CTE as if it was a subquery.
I don't understand why you are saying, "CTE's are missing the property to use it as input for another query" while it is just the opposite. You can use a CTE as an input for another query. CTEs are referred as "auxillary queries", that you can use DML within and as an input for other DML queries (saying DML I mean, SELECT, DELETE, UPDATE, INSERT and not just SELECT as you would do with a subquery).
I can of course use a CTE with EXISTS(), IN() etc. The syntax being a little bit different doesn't mean you can't use it.

I would accept the challenge with pleasure, just make me understand what you really mean by giving some examples. You are trying to put constraints on what should be done or not but still that is OK. But let me to understand what you really mean by hierarchic queries. Do you mean 'recursive' queries that use anchors with a self referencing query? ie:

with CTE(n) as
(
  select 1
  union
  select n+1 from CTE where n < 100
)
select n from CTE;

or:

WITH CTE (n, cdow)
AS (SELECT 0,DATENAME(dw, 0)
    UNION ALL
    SELECT n + 1,DATENAME(dw, n + 1)
    FROM CTE WHERE n < 6)
SELECT * FROM CTE;

"The query does not reference the CTE more than once." Why is that? Are you saying a query doesn't have a need to do such a thing at all? Or just because you wouldn't do the same thing with a subquery?

Still just enlighten me with samples and that is OK with your constraints.

And most probably, your forgotten constraint is to do that with only MS SQL Server (which is not as good as others in using CTEs). Still OK :)