ff7e574b   
   From: esquel@sommarskog.se   
      
   bill (billmaclean1@gmail.com) writes:   
   > But a reasonable question some may ask me is "why the ROW_NUMBER()   
   > function in the first place?"  I realize that order is not   
   > relationally significant.  However, I am working with a commercial   
   > system, and unfortunately order IS significant for parts of it, and I   
   > can't avoid the issue.  The ROW_NUMBER() takes care of the problem.   
      
   What problem? You row_number() because you want number rows in your result   
   set, or you use the number to filter later. If you are using row_number()   
   with the intention that you will get a certain order in the output, you   
   have a bug you need to fix.   
      
   > I think with a hard coded WHERE clause, the ROW_NUMBER() operator was   
   > limiting itself to just the values listed in the partition.  With the   
   > join, the ROW_NUMBER() was operating on the entire result set, even   
   > though it should have limited to jus the values in some_table.   
      
   It is impossible to tell without seeing the query, but adding row_number   
   in a CTE can prevent the optimizer from recasting computation order,   
   since that would affect the result from row_number().   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)