I need to return the last consignment number grouped by a `reference3` (only when there are more then 1 record with the same `reference3`.

Is there a way to create the query below using the `partition by` command? I have done an attempt:

**Attempt using partition by:** (Didn't work :/)

```
SELECT
    SubAccountId,
    Reference3,
    ConsignmentNumber,
    MAX(ConsignmentNumber) OVER(PARTITION BY SubAccountId, Reference3) AS LastConsignmentNumber

FROM [CxGen].[dbo].[consignment] c   
GROUP BY SubAccountId, Reference3
HAVING COUNT(*) > 1

```

**Original query:**

```
;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) > 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 > GETDATE()-1

ORDER  BY C.Reference3, 
          C.SubaccountId, 
          C.ConsignmentNumber

```

**Expected result:**

```
    +--------------+-----------------+------------+-------------------+-----------------+
    | 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 |
    +--------------+-----------------+------------+-------------------+-----------------+

```