>Cetin
>
>You are refering to a recursive query, which I (I admit the mischaracterization as recursive CTEs can be use in creating records as well) referred to as for solving hierarchical problems. This is where I almost exclusively use them for.
>
>The challenge was to find me an example where using CTE as a subquery would lead to any significant advantage.

I've run into cases where I need the same subquery in multiple parts of a query. A CTE lets me write it once and use it repeatedly. Don't have an example handy, but it shouldn't be hard to think of one.

FWIW, I also find queries with CTEs (especially when there are several) far more readable than queries with lots of derived table. The case where I think readability is most aided is when otherwise, I'd have a long nested set of queries. In my view, those are so hard to read, and the CTE lays things out neatly so you can follow what's happening.

Tamar