WITH keyword in SQL

A feature I didn't know in SQL 2005: WITH can be used to create ad-hoc table-like entities within a query (they call it CTE for Common Table Expression). Check it out here and here.