>>ReturnId BatchId PartId UserId WarehouseId SerialNo Details Status ActionDate >>----------- ----------- ----------- ----------- ----------- ------------------------------ ---------------------------------------- ------ ----------------------- >>14 1 14961 47 20 NULL The Details 1 2009-06-17 11:00:19.623 >>15 1 14962 47 20 NULL Replaced part 1 2009-06-17 11:00:19.623 >>16 1 14963 47 20 NULL Replaced part 1 2009-06-17 11:00:19.623 >>>>
>>CREATE PROCEDURE ap_GetReturnsForWarehouse >> @WarehouseId INT, >> @Status TINYINT = NULL >> >>AS >>BEGIN >> >> SELECT r.*, >> p.PartNo, >> p.Description, >> u.FullName, >> w.Caption AS Warehouse, >> r.Status >> 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) >> ORDER BY BatchId, p.PartNo, r.SerialNo DESC >> >>END >>>>
> 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) OrderedResultSet > where OrderedResultSet.RowNum = 1 > ORDER BY BatchId, p.PartNo, r.SerialNo DESC >I'm getting "Incorrect syntax near 'OrderedResultSet'.". Using SQL 2005.