>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