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

Now I get

Msg 8156, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
The column 'Status' was specified multiple times for 'OrderedResultS'.
Msg 4104, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
The multi-part identifier "OrderedResultSet.RowNum" could not be bound.
Msg 4104, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
The multi-part identifier "p.PartNo" could not be bound.
Msg 4104, Level 16, State 1, Procedure ap_GetReturnsForWarehouse, Line 23
The multi-part identifier "r.SerialNo" could not be bound.

There are elements in that query I have never seen before. Very confusing.