>>>>I see another problem in your query. You already got r.*, why you're getting r.Status again at the end of it?
>>>
>>>Like I said, I used what you gave me earlier. I don't understand alot of what you have here so I can't comment. All I want it the first record from each batch. I seems to me that this should be alot easier to do than this.
>>
>>Try now - I believe it should work
>>
>>select * from (SELECT r.*,
>>>>>> p.PartNo,
>>>>>> p.Description,
>>>>>> u.FullName,
>>>>>> w.Caption AS Warehouse,
>>>>>> 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)) OrderedResultS
>>>>>> where OrderedResultS.RowNum = 1
>>>>>> ORDER BY BatchId
>>
>>The query is very simple - I was just making some typos.
>>
>>Or review the link I posted in other message for alternative solutions.
>
>I have been trying this, but I only get back the first row in the table.
>
>
>
>select *
> from returns
> where actiondate =
> (select min(actiondate)
> from returns R
> where R.batchid = returns.batchid)
>
>
>I don't know quite how to do this
That's the alternative syntax.
But have you been able to get my query to work?
select * from (select *, row_Number() OVER (partition by BatchID Order BY ActionDate) as RowNum from returns) R
where RowNum = 1
If it's not broken, fix it until it is.
My Blog