Look closely into this query - you should use either OrderedResultSet or OrderedResultS, but not both.

Or just use O for the alias - it really doesn't matter much.

>>>>>Now I get
>>>>>

>>>>>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.
>>>>>Msg 4104, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
>>>>>The multi-part identifier "p.PartNo" could not be bound.
>>>>>Msg 4104, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
>>>>>The multi-part identifier "r.SerialNo" could not be bound.
>>>>>

>>>>>
>>>>>There are elements in that query I have never seen before. Very confusing.
>>>>
>>>>The inner part ( with row_number() ) was supposed to be a derived table. I'm guessing you didn't use
>>>>
>>>>select * from (derived table select with row_number()) OrderedRecordSet (alias I gave to the derived table) where rowNum = 1
>>>>
>>>>Can you post your exact query now to correct ?
>>>
>>>I used exactly what you posted earlier:
>>>
>>>

>>>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 OrderedResultSet.RowNum = 1
>>>		ORDER BY BatchId, p.PartNo, r.SerialNo DESC
>>>

>>
>>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.