f1371151   
   From: esquel@sommarskog.se   
      
   Fred. (ghrno-google@yahoo.com) writes:   
   > The WITH clause works in a manner similar to a derived table, but sets   
   > up the syntax so that it can be referenced more than once.  In this   
   > instance it allows the table to joined to itself rather than a second   
   > derivation of itself as would happen using the derived table syntax.   
      
   Correct. This is known as a CTE, Common Table Expression.   
      
   > The OVER construct (just what is the syntax term?) specifies that row   
   > numbering restarts for each new value of the field specified in   
   > PARTITION BY, with the rows arranged within each partition as   
   > specified in ORDER BY.   
      
   Correct. It's called the OVER clause.   
      
   > Do you know if the engine optimizes by indexing the WITH table on the   
   > PARTTION field and the field receiveing row_number()?   
      
   The CTE is purely logical. Some products are able to materialize the CTE as   
   optimisation, but SQL Server is not. Thus, in some cases it can be better   
   to use a temp table to hold the numbering.   
      
   Lennart mention LEAD and LAG. They are also subclauses within OVER. They   
   are not available in SQL Server currently, but in products that have LAG and   
   LEAD, this can be written as a straight query without a self-join.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)