title: SQL: Count of rows before and after given row
body: <p>The problem is that we are to get total number of rows before and after a given row (identified by, say, primary key). </p>
  
  <p>I have tried following in T-SQL (MSSQL 2008). It is giving right result, but I don't know if this is best way to do it.</p>
  
  <pre><code>;WITH cte_before AS 
  (
      SELECT ROW_NUMBER() OVER ( Order By CustomerId ) [Row Number], customerid, 
             firstName 
      FROM SalesLT.Customer
   ),
  cte_nums AS 
  (
      SELECT ROW_NUMBER() OVER ( Order By CustomerId ) [Row Number1] 
      FROM SalesLT.Customer
  )
  SELECT [Row Number]-1 [before], MAX([Row Number1]) - [Row Number]
         , CustomerID, FirstName 
  FROM cte_nums, cte_before
  GROUP BY [Row Number], CustomerID, FirstName
  HAVING CustomerID = 55
  </code></pre>
  
  <p>How can we improve it in T-SQL and how can we accomplish in other SQL dialects and server (like Oracle, MySQL, sqlite, FireBird etc.)</p>