title: Partition By in Nested Select Returning Strange Results
body: <p>I came across a strange behavior in SQLServer 2008 that I didn't understand. I wanted to quickly just pair a unique customer with a unique payment.</p>
  
  <p>Using this query, I get the results that I am expecting. Each CustomerId is paired with a different PaymentId.</p>
  
  <pre><code>SELECT CustomerId, PaymentId, RowNumber1, RowNumber2
  FROM (
      SELECT
          c.Id as CustomerId,
          p.Id as PaymentId,
          ROW_NUMBER() OVER (PARTITION BY p.Id ORDER BY p.Id) AS RowNumber1,
          ROW_NUMBER() OVER (PARTITION BY c.Id ORDER BY c.Id) AS RowNumber2
      FROM Customer as c
      CROSS JOIN Payment as p
  ) AS INNERSELECT WHERE RowNumber2 = 1
  
  +------------+-----------+------------+------------+
  | CustomerId | PaymentId | RowNumber1 | RowNumber2 |
  +------------+-----------+------------+------------+
  |          4 |         1 |          1 |          1 |
  |          5 |         2 |          2 |          1 |
  +------------+-----------+------------+------------+
  </code></pre>
  
  <p>However, if I remove the RowNumber1 column from the outer select, the results seem to change. Now every value of PaymentId is 1, even though I did not touch the inner select statement.</p>
  
  <pre><code>SELECT CustomerId, PaymentId, RowNumber2
  FROM (
      SELECT
          c.Id as CustomerId,
          p.Id as PaymentId,
          ROW_NUMBER() OVER (PARTITION BY p.Id ORDER BY p.Id) AS RowNumber1,
          ROW_NUMBER() OVER (PARTITION BY c.Id ORDER BY c.Id) AS RowNumber2
      FROM Customer as c
      CROSS JOIN Payment as p
  ) AS INNERSELECT WHERE RowNumber2 = 1
  
  +------------+-----------+------------+
  | CustomerId | PaymentId | RowNumber2 |
  +------------+-----------+------------+
  |          4 |         1 |          1 |
  |          5 |         1 |          1 |
  +------------+-----------+------------+
  </code></pre>
  
  <p>Could anyone explain to me why removing a column from the outer select causes the values in the PaymentId column to change? What other method could I use to achieve my desired goal without needing the row numbers in the result set?</p>