Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Return First Record Of Each Batch
Message
General information
Forum:
Microsoft SQL Server
Category:
Other
Miscellaneous
Thread ID:
01406654
Message ID:
01406658
Views:
44
>>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.
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform