>>oops - remove aliases in the last line ORDER BY
>>
>> ORDER BY BatchId - we don't even need more orders - since we get only one record per batch
>>
>>>select * from (SELECT r.*,
>>> p.PartNo,
>>> p.Description,
>>> u.FullName,
>>> w.Caption AS Warehouse,
>>> r.Status, Row_Number() OVER (Partition by BatchID ORDER BY p.PartNo, r.SerialNo DESC) as RowNum
>>> FROM Returns r
>>> JOIN Parts p ON p.PartId = r.PartId
>>> JOIN Warehouses w ON w.WarehouseId = r.WarehouseId
>>> JOIN Users u ON u.UserId = r.UserId
>>> WHERE r.WarehouseId = @WarehouseId AND
>>> (@Status IS NULL or r.Status = @Status)) OrderedResultS
>>> where OrderedResultS.RowNum = 1
>>> ORDER BY BatchId
>
>Still getting
>
>Msg 8156, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
>The column 'Status' was specified multiple times for 'OrderedResultS'.
>Msg 4104, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
>The multi-part identifier "OrderedResultSet.RowNum" could not be bound.
I see another problem in your query. You already got r.*, why you're getting r.Status again at the end of it?
If it's not broken, fix it until it is.
My Blog