Only show value of Max rows with partition by?

the title might be a bit off however i'm trying to remove the values of a row without removing the actual row.

This is my table:

SELECT ID,CustomerID,Weight FROM Orders

What am i trying to accomplish is this:

The MAX() value of ID Group By CustomerID that would give me null values in Weight where max and group by is not set

Is it possible to do this in one line? with a partiton by?

Something like:

SELECT MAX(ID) over (partition by CustomerID,Weight).... I know this is wrong but if possible to do without a join or CTE and only in one line in the select statement that would be great.

Solution

One possible approach is using ROW_NUMBER:

SELECT 
    ID,
    CustomerID,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY ID DESC) = 1 THEN [Weight]
        ELSE Null
    END AS [Weight]
FROM #Orders
ORDER BY ID

Input:

CREATE TABLE #Orders (
    ID int,
    CustomerID int,
    [Weight] int
)
INSERT INTO #Orders
    (ID, CustomerID, [Weight])
VALUES
    (1, 11, 100),
    (2, 11, 17),
    (3, 11, 35),
    (4, 22, 26),
    (5, 22, 78),
    (6, 22, 10030)

Output:

ID  CustomerID  Weight
1   11          NULL
2   11          NULL
3   11          35
4   22          NULL
5   22          NULL
6   22          10030