I'm trying to only return the data needed, but can't figure out a way to get only the data that is request. The issue I'm having is the table can have multiple records of the samething but one column is updated with different data.
Want I'm trying to do is get the latest entry return code along with the name, product, and a count of records by name and product.
ID | name | product | ordernum| return code
1 john abc 14423 0
2 john abc 14423 1
3 peter ade 13324 1432
4 peter abed 43554 1324
id | name | product | ordernum | return code | count
2 john abc 14423 1 2
4 peter abed 43554 1324 2
You can use a
ROW_NUMBER() windowed function to get the result:
;With Cte As ( Select Id, Name, Product, OrderNum, [Return Code], Row_Number() Over (Partition By Name, Product Order By Id Desc) As RN, Count(*) Over (Partition By Name, Product) As Count From YourTable ) Select Id, Name, Product, OrderNum, [Return Code], Count From Cte Where RN = 1