>>>I have the following data
>>>
>>>

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

>>>
>>>I have the following query, improved with Nadia's help:
>>>
>>>

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

>>>
>>>I need to get back only the first record from each back. Not sure what the syntax is for this. Anyone?
>>
>>SQL Server 2005 and up solution:
>>

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

Good point - I forgot to close parens

(@Status IS NULL or r.Status = @Status)) OrderedResultSet