8c401eec   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Fri, 17 Sep 2010 09:10:53 -0700 (PDT), Dom wrote:   
      
   >I was always told that the order of execution in T-SQL was FROM,   
   >SELECT, WHERE.  This turns out to be important if you want to avoid   
   >certain errors, such as "divide by zero" error.   
   >   
   >But now look at the following table:   
   >   
   >ID     Name     Pos   
   >1      Dom       1   
   >1      Kim         2   
   >1      Sam       3   
   >   
   >   
   >In the following, it looks like the windowing function is done AFTER,   
   >not BEFORE, the where clause:   
   >   
   >   
   >select     Name, max (Pos) over (partition by Group_ID)   
   >from       T_Table   
   >where     Last_Name < 'S'   
   >   
   >The value for max (Pos) is 2, not 3.   
   >   
   >Why did that happen?   
      
   Hi Dom,   
      
   The WHERE clause uses the Last_Name column, not the Name column that is   
   used in the SELECT list.   
   If that is a typo in your post, then I can't reproduce the issue. Here   
   is what I tested:   
      
   CREATE TABLE test   
          (ID int NOT NULL,   
           name varchar(10) NOT NULL,   
           pos int NOT NULL,   
           PRIMARY KEY (ID, pos));   
   go   
   INSERT INTO test   
   SELECT 1, 'Dom', 1 UNION ALL   
   SELECT 1, 'Kim', 2 UNION ALL   
   SELECT 1, 'Sam', 3;   
   go   
   SELECT name, MAX(pos) OVER(PARTITION BY ID)   
   FROM   test   
   WHERE  name < 'S'   
   go   
   DROP TABLE test   
   go   
      
   And the results:   
   name   
   ---------- -----------   
   Dom        2   
   Kim        2   
      
   If you want me to investigate further, then please post some code to   
   reproduce the problem. See www.aspfaq.com/5006 for some suggestions how   
   to assemble the required information.   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)