>>I'm trying to return the last row from a batch:
>>
>>

>>select inv.*, inv2.actiondate AS ActDate
>>	from inventory inv
>>	left outer join inventory inv2 on inv2.batchid = inv.batchid and  
>>	inv2.actiondate > inv.actiondate
>>	where ActDate IS NULL
>>

>>
>>If I leave off the WHERE clause I get back 7 rows, with the last one having a null ActDate. If I add on the WHERE clause, I get
>>"Invalid column name 'ActDate'".
>>
>>What's wrong here????
>
>You can not use column's aliases in WHERE clause, only in ORDER BY clause. Use the actual field name, e.g.
>
>WHERE inv2.ActionDate IS NULL
>
>Though I don't understand, what you're trying to achieve here.



I'm trying to modify this query so that it only looks at the latest record in a batch.

SELECT i.*, p.PartNo, p.Description, l.Location, w.WarehouseCode, w.Caption AS Warehouse, w.WarehouseId
	FROM Inventory i
	JOIN Parts p ON p.PartId = i.PartId
	LEFT JOIN Locations l ON l.LocationId = i.LocationId
	LEFT JOIN Warehouses w ON w.WarehouseId = i.WarehouseId
	WHERE p.PartNo = @PartNo AND
		  (@SerialNo IS NULL OR i.SerialNo = @SerialNo) AND
		  w.WarehouseId = @WarehouseId AND
		  i.LocationId IS NOT NULL AND
		  l.RemoveFromInv = 0