>I have a datetime column called ActionDate on an inventory record. Each time a change is made a new record is inserted so that
>I will always have a history.
>
>I want to get back the last inventory record for a warehouse for a part #. Here's what I have so far. I'm getting back all records:
>
>

>SELECT i.InventoryId, i.LocationId, i.LocationInfo, l.Location, p.PartNo, p.Description, i.SerialNo
>	FROM Inventory i
>	JOIN Locations l ON l.LocationId = i.LocationId
>	JOIN Parts p ON p.PartId = i.PartId
>	WHERE i.WarehouseId = @WarehouseId AND
>		  p.PartNo = @PartNo AND
>		  (@SerialNo IS NULL OR i.SerialNo = @SerialNo) AND
>		  i.ActionDate =
>			(SELECT MAX(i2.ActionDate)
>				FROM Inventory i2
>				WHERE i2.InventoryId = i.InventoryId
>
>

SQL Server 2005 and up solution

select * from (SELECT i.InventoryId, i.LocationId, i.LocationInfo, l.Location, 
p.PartNo, p.Description, i.SerialNo, Row_Number() OVER (partition by i.InventoryID ORDER by ActionDate DESC) AS RowNum
	FROM Inventory i
	JOIN Locations l ON l.LocationId = i.LocationId
	JOIN Parts p ON p.PartId = i.PartId
	WHERE i.WarehouseId = @WarehouseId AND
		  p.PartNo = @PartNo AND
		  (@SerialNo IS NULL OR i.SerialNo = @SerialNo) ) OrderedSet where RowNum = 1