title: Using PARTITION BY in a query to return max value of a select
body: <p>I need to return the last consignment number grouped by a <code>reference3</code> (only when there are more then 1 record with the same <code>reference3</code>.</p>
  
  <p>Is there a way to create the query below using the <code>partition by</code> command? I have done an attempt:</p>
  
  <p><strong>Attempt using partition by:</strong> (Didn't work :/)</p>
  
  <pre><code>SELECT
      SubAccountId,
      Reference3,
      ConsignmentNumber,
      MAX(ConsignmentNumber) OVER(PARTITION BY SubAccountId, Reference3) AS LastConsignmentNumber
  
  FROM [CxGen].[dbo].[consignment] c   
  GROUP BY SubAccountId, Reference3
  HAVING COUNT(*) &gt; 1
  </code></pre>
  
  <p><strong>Original query:</strong></p>
  
  <pre><code>;WITH DuplicatedConsignments AS 
  (
      SELECT Reference3, 
             SubAccountId, 
             Max(ConsignmentDate) AS ConsignmentDate
  
      FROM   [CxGen].[dbo].[consignment] 
      WHERE  Reference3 IN 
              (
                  SELECT Reference3 
                  FROM   [CxGen].[dbo].[Consignment] 
                  GROUP  BY Reference3, SubAccountId 
                  HAVING Count(Reference3) &gt; 1
              ) 
  
      GROUP  BY Reference3, SubAccountId
  ) 
  SELECT C.SubAccountid, 
         C.ConsignmentDate, 
         C.Reference3, 
         C.ConsignmentNumber, 
         C.CreatedOn 
  FROM   [CxGen].[dbo].[consignment] C 
         INNER JOIN DuplicatedConsignments dc 
                 ON C.reference3 = dc.reference3 
                    AND C.consignmentdate = dc.consignmentdate 
                    AND C.subaccountid = dc.subaccountid 
  
  WHERE C.ConsignmentDate &gt; GETDATE()-1
  
  ORDER  BY C.Reference3, 
            C.SubaccountId, 
            C.ConsignmentNumber
  </code></pre>
  
  <p><strong>Expected result:</strong></p>
  
  <pre><code>    +--------------+-----------------+------------+-------------------+-----------------+
      | SubAccountid | ConsignmentDate | Reference3 | ConsignmentNumber |    CreatedOn    |
      +--------------+-----------------+------------+-------------------+-----------------+
      |         2070 | 7/09/2017 14:09 |    1279152 | DNZ0322457        | 7/09/2017 14:09 |
      |         1065 | 7/09/2017 15:42 |    1647907 | AULGP00031023     | 7/09/2017 15:42 |
      |         1065 | 7/09/2017 9:30  |    1653615 | AULGP00031009     | 7/09/2017 9:30  |
      |         1085 | 6/09/2017 16:52 |    1661307 | 6X31222878        | 6/09/2017 16:52 |
      |         1085 | 6/09/2017 16:52 |    1661308 | 6X31222877        | 6/09/2017 16:52 |
      |         1085 | 6/09/2017 16:40 |    1661311 | 6X31222871        | 6/09/2017 16:40 |
      |         1085 | 6/09/2017 16:30 |    1661312 | 6X31222853        | 6/09/2017 16:30 |
      |         1085 | 6/09/2017 16:56 |    1661318 | 6X31222879        | 6/09/2017 16:56 |
      |         1085 | 6/09/2017 16:41 |    1661320 | 6X31222872        | 6/09/2017 16:41 |
      |         1085 | 6/09/2017 16:42 |    1661321 | 6X31222873        | 6/09/2017 16:42 |
      |         1085 | 6/09/2017 16:43 |    1661322 | 6X31222874        | 6/09/2017 16:43 |
      |         1085 | 6/09/2017 17:11 |    1661323 | IA0017426243      | 6/09/2017 17:11 |
      |         1085 | 6/09/2017 16:44 |    1661324 | ia0017426030      | 6/09/2017 16:44 |
      |         1085 | 6/09/2017 16:51 |    1661325 | 6X31222876        | 6/09/2017 16:51 |
      |         1085 | 6/09/2017 16:51 |    1661326 | ia0017426031      | 6/09/2017 16:51 |
      |         1085 | 6/09/2017 16:55 |    1661352 | IA0017426032      | 6/09/2017 16:55 |
      |         1085 | 7/09/2017 16:21 |    1661440 | IA0017426261      | 7/09/2017 16:21 |
      |         2060 | 7/09/2017 15:56 |    1662227 | 2.3601E+12        | 7/09/2017 15:56 |
      |         2060 | 7/09/2017 15:49 |    1663004 | NULL              | 7/09/2017 15:49 |
      +--------------+-----------------+------------+-------------------+-----------------+
  </code></pre>