title: Query to get highest ROW_NUM() value of a column AS only RANK() value of another column
body: <p>It's question #105 on <a href="http://sql-ex.ru" rel="nofollow noreferrer">http://sql-ex.ru</a></p>
  
  <p>This is the database schema : <a href="https://i.stack.imgur.com/l7qDy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l7qDy.png" alt="Database Schema "></a></p>
  
  <p>The question : 
  Statisticians Alice, Betty, Carol and Diana are numbering rows in the Product table.
  Initially all they have sorted the table rows in ascending order of the makers' names.
  Alice is assigning a new number to each row, in so doing she is ordering the rows of the same maker by model ascending.
  Three other statisticians are assigning the identical number to all rows of the same maker.</p>
  
  <ul>
  <li><p>Betty assigns the numbers starting from one, every next maker increases the number by 1.</p></li>
  <li><p>Carol gives such number to a maker, which number Alice would give for the first model of this maker.</p></li>
  <li><p>Diana gives such number to a maker, which number Alice would give for the last model of this maker.</p></li>
  </ul>
  
  <p>Output: maker, model, numbers which have been assigned to the table rows by Alice, Betty, Carol, and Diana respectively.</p>
  
  <p>I came up with this query already for Alice(A),Betty(B),Carol(C) </p>
  
  <pre><code>SELECT maker, model, 
   ROW_NUMBER() OVER (ORDER BY maker,model ASC) A,
   DENSE_RANK() OVER(ORDER BY maker) B,
   RANK() OVER (ORDER BY maker) C
   FROM product
   ORDER BY model ASC
  </code></pre>
  
  <p>But I'm having trouble finding the solution for Diana(D) Column.</p>
  
  <p>According to the site, this is the result a correct query should give : 
  <a href="https://i.stack.imgur.com/6gFmO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6gFmO.png" alt="Results a correct query should give"></a></p>
  
  <p>Thanks for your help, time and comprehension.</p>