title: Incorrect syntax near the keyword 'OVER' when executing one query but not the other
body: <p>I am doing some work on a query in iBatis that deals with pagination.</p>
  
  <p>When I run the following query, it seems to execute fine:</p>
  
  <pre><code>SELECT
      COUNT(1) OVER (PARTITION BY NULL) AS TotalRows,
      ROW_NUMBER() OVER (ORDER BY [User].Username ASC) AS RowNum
   FROM
      dbo.[USER]
   LEFT OUTER JOIN Account 
                   on [User].AccountId = Account.AccountID
   LEFT OUTER JOIN UserPasscodeAccount on [User].Id = UserPasscodeAccount.UserID
   LEFT OUTER JOIN PasscodeAccount 
                   on UserPasscodeAccount.PasscodeAccountID = PasscodeAccount.ID
   WHERE
      [User].Active = 1
  </code></pre>
  
  <p>However, when I attempt to substitute a different subset of data as the value for "TotalRows", I get the following T-SQL error:</p>
  
  <pre><code>Incorrect syntax near the keyword 'OVER'
  </code></pre>
  
  <p>Please see the following query that gives me errors:</p>
  
  <pre><code>SELECT 
      COUNT(1) OVER (PARTITION BY NULL) FROM (
      SELECT DISTINCT
      [User].Id as "User_Id",
      [User].AccountId as "User_AccountId",
      [User].AccountId as "User_AccountAssociation",
      [User].Username as "User_Username",
      [User].Password as "User_Password",
      [User].Name as "User_Name",
      [User].PhoneNumber as "User_PhoneNumber",
      [User].EmailAddress as "User_EmailAddress",
      [User].CreatedOn as "User_CreatedOn",
      [User].CreatedBy as "User_CreatedBy",
      [User].ChangedOn as "User_ChangedOn",
      [User].ChangedBy as "User_ChangedBy",
      [User].LastLogin as "User_LastLogin",
      [User].TemporaryPassword as "User_TemporaryPassword",
      [User].Active as "User_Active",
      Account.AccountID as "Account_AccountId",
      Account.CompanyID as "Account_CompanyId",
      Account.AccountName as "Account_AccountName",
      Account.AccountType as "Account_AccountType",
      Account.LoginID as "Account_LoginID",
      Account.LoginPassword as "Account_LoginPassword",
      Account.AccountBillingCode as "Account_AccountBillingCode",
      Account.DefaultTimeZone as "Account_DefaultTimeZone",
      Account.AuthorizationLevelID as "Account_AuthorizationLevelID",
      Account.CreatedBy as "Account_CreatedBy",
      Account.ChangedBy as "Account_ChangedBy",
      Account.RegistrationFormID as "Account_RegistrationFormID",
      Account.CreatedOn as "Account_CreatedOn",
      Account.ChangedOn as "Account_ChangedOn",
      Account.DataPresenterID as "Account_DataPresenterID",
      Account.DefaultLoginPIN as "Account_DefaultLoginPIN",
      Account.CopyrightText as "Account_CopyrightText",
      Account.LiveParticipantListEnabled as "Account_LiveParticipantListEnabled",
      Account.MaxLiveParticipantListusers as "Account_MaxLiveParticipantListusers",
      Account.LoginText as "Account_LoginText",
      Account.CMeetingIntegration AS "Account_CMeetingIntegration"
  FROM
      [User]
      LEFT OUTER JOIN Account on [User].AccountId = Account.AccountID
      LEFT OUTER JOIN UserPasscodeAccount on [User].Id = UserPasscodeAccount.UserID
      LEFT OUTER JOIN PasscodeAccount on UserPasscodeAccount.PasscodeAccountID = PasscodeAccount.ID
  WHERE
      [User].Active = 1
  GROUP BY [User].Id,[User].AccountId,[User].AccountId,[User].Username,[User].Password,[User].Name,[User].PhoneNumber,[User].EmailAddress,
           [User].CreatedOn,[User].CreatedBy,[User].ChangedOn,[User].ChangedBy,[User].LastLogin,[User].TemporaryPassword,[User].Active,
           Account.AccountID,Account.CompanyID,Account.AccountName,Account.AccountType,Account.LoginID,Account.LoginPassword,Account.AccountBillingCode,
           Account.DefaultTimeZone,Account.AuthorizationLevelID,Account.CreatedBy,Account.ChangedBy,Account.RegistrationFormID,Account.CreatedOn,
           Account.ChangedOn,Account.DataPresenterID,Account.DefaultLoginPIN,Account.CopyrightText,Account.LiveParticipantListEnabled,
           Account.MaxLiveParticipantListusers,Account.LoginText,Account.CMeetingIntegration)      
   AS TotalRows,
   ROW_NUMBER() OVER (ORDER BY [User].Username ASC) AS RowNum
   FROM
      dbo.[USER]
   LEFT OUTER JOIN Account on [User].AccountId = Account.AccountID
   LEFT OUTER JOIN UserPasscodeAccount on [User].Id = UserPasscodeAccount.UserID
   LEFT OUTER JOIN PasscodeAccount on UserPasscodeAccount.PasscodeAccountID = PasscodeAccount.ID
   WHERE
      [User].Active = 1
  </code></pre>
  
  <p>Note that the above query will execute fine until it hits the following line:</p>
  
  <pre><code>ROW_NUMBER() OVER (ORDER BY [User].Username ASC) AS RowNum
  </code></pre>
  
  <p>I guess my question is "Why does the first query run fine, but when I attempt to use a subset of data to populate 'TotalRows' I get an incorrect syntax error regarding the OVER clause for 'RowNum' as in the statement above?"</p>
  
  <p>Please bear with me as I am not very well versed in T-SQL.</p>
  
  <p>If you have any questions or need further clarification I will be happy to assist.</p>