CrazyITWannaBe CrazyITWannaBe - 5 months ago 6
SQL Question

Filtering selected data from a table

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.

same table:

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


So the return information should look something like this.

id | name | product | ordernum | return code | count
2 john abc 14423 1 2
4 peter abed 43554 1324 2


Any ideas on how I can do this?

Answer

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