asked
SQL Group by query - needs your attention
I am stuck in a sql query. I have data like: IdentityId ProductId TypeId Rating 3 1 1 9 7 1 2 3 9 500 1 7 2 500 2 5 8 777 4 5 12 777 3 8 11 999 4 1 I need to the maximum Rating of each Product + i need the typeId associated with that Rating. So the Product 1 have the maximum Rating 9 & the TypeId associated is 1 Product 500 have the maximum Rating 7 & the TypeId associated is 1 Product 777 have the maximum Rating 8 & the TypeId associated is 3 Product 999 have the maximum Rating 1 & the TypeId associated is 4 **like below output:** ProductId TypeId Rating 1 1 9 500 1 7 777 3 8 999 4 1 Please ask me if the question is not clear. Thanks for your valuable time & help.
Based upon your data and required output, below query would be good enough (Hopefully) :-) ; WITH CTE AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY ProductId ORDER BY Rating DESC ) ROWID , ProductId , TypeId , RATING FROM YOURTABLE ) SELECT ProductId , TypeId , RATING FROM [CTE] WHERE [ROWID] = 1
