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