title: Row_number over partition and find the max rn value
body: <p>I started from the following table:</p>
  <pre><code>+---+-----+---------+----------+----------+-------------+
  | 1 | ID  | OrderNO | PartCode | Quantity | DateEntered |
  | 2 | 417 | 2144    | 44917    | 100      | 40773       |
  | 3 | 418 | 7235    | 11762    | 5        | 40773       |
  | 4 | 419 | 9999    | 60657    | 100      | 40773       |
  | 5 | 420 | 9999    | 60657    | 90       | 40774       |
  +---+-----+---------+----------+----------+-------------+
  </code></pre>
  <p>to this:</p>
  <pre><code>+---+---------+----------+----------+-------------+----+
  | 1 | OrderNO | PartCode | Quantity | DateEntered | rn |
  | 2 | 2144    | 44917    | 100      | 40773       | 1  |
  | 3 | 7235    | 11762    | 5        | 40773       | 1  |
  | 4 | 9999    | 60657    | 100      | 40773       | 1  |
  | 5 | 9999    | 60657    | 90       | 40774       | 2  |
  +---+---------+----------+----------+-------------+----+
  </code></pre>
  <p>using the query fo this <a href="https://stackoverflow.com/a/7118233/1008588">answer</a>.</p>
  <pre><code>select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
  from YourTable) as T
  </code></pre>
  <p>Now, I am trying to add a field &quot;rn_max&quot;, shich is the max &quot;rn&quot; of each OrderNO and get the following result:</p>
  <pre><code>+---+---------+----------+----------+-------------+----+--------+
  | 1 | OrderNO | PartCode | Quantity | DateEntered | rn | rn_max |
  | 2 | 2144    | 44917    | 100      | 40773       | 1  | 1      |
  | 3 | 7235    | 11762    | 5        | 40773       | 1  | 1      |
  | 4 | 9999    | 60657    | 100      | 40773       | 1  | 2      |
  | 5 | 9999    | 60657    | 90       | 40774       | 2  | 2      |
  +---+---------+----------+----------+-------------+----+--------+
  </code></pre>
  <p>I could get this result calculating the max of each OrderNO with the query</p>
  <pre><code>SELECT OrderNO,MAX(rn) AS 'rn_max'
  FROM (
  select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
  from YourTable
  ) T
  Group by OrderNO
  </code></pre>
  <p>and then use a left join from YourTable.
  Is there a shortest way to count the rows with the same OderNo? I've tried to add <code>row_number() over(partition by OrderNO) as rn_max</code> at the query but I need to write an order by clause.</p>