question

yaqubonnet avatar image
yaqubonnet 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.
sql-server-2008group-bytop
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Usman Butt: Thanks
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.