>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