title: Get last purchase along with price
body: <p>I want to get the Catalog of a selected supplier along with the last purchase price which is the column <code>Costs</code> in the CommandDetails table as shown bellow :</p>
  
  <p><code>Product (idproduct, productName)</code></p>
  
  <p><code>Command (idCommand, CommandCode, CommandDate, idSupplier)</code></p>
  
  <p><code>CommandDetails(idCommandDetails, idProduct, Qty, idCommand, Costs)</code></p>
  
  <p><code>Supplier (idSupplier, SupplierName, SupplierAddress)</code></p>
  
  <p><code>SupplierCatalog (idSupplier, idProduct)</code></p>
  
  <p>I tried the <code>row_number() Over (Partition by ...)</code> and other methods but i'm missing something, my brain said enough.</p>
  
  <p>Desired Result : </p>
  
  <pre><code>--------------------------------------------------
  | SupplierName| ProductName | CommandDate | Costs|
  --------------------------------------------------
  |    SUP1     |   P1        |  01/01/2018 | 3,06 |
  |    SUP1     |   P6        |  01/01/2018 | 1,65 |
  |    SUP1     |   P8        |  03/01/2018 | 5,20 |
  |    SUP1     |   P9        |  05/01/2018 | 8,00 |
  |    SUP1     |   P10       |  01/01/2018 | NULL |
  --------------------------------------------------
  </code></pre>
  
  <p>Null in Costs for P10 when the product has never been ordered.</p>
  
  <p>My last attempt :</p>
  
  <pre><code>SELECT 
  *
  FROM
  (SELECT 
      Sct.idsupplier,
      SCt.idProduct,
      SCD.PurchasePriceCmd Costs,
      SCD.Qty,
      P.ProCode,
      P.ProName, 
      Row_number() OVER(PARTITION BY Sct.idProduct order by P.ProCode) rn
  FROM SupplierCatalog SCt
      LEFT JOIN CommandDetails SCD
          ON SCD.idProduct = SCat.idProduct 
      LEFT JOIN Command a
          ON a.idCommand = SCD.idCommand
      LEFT OUTER JOIN StoreCommand b
          ON a.idCommand = b.idCommand
      INNER JOIN Product P
          ON P.idProduct = SCt.idProduct) t
  where Sct.idSupplier = 4 and rn = 1
  </code></pre>