CODEWITHSUNDEEP
sqlsql-server-2008
Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

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.

Upvotes: 1

Views: 135

Answers (5)

Rashmi Kant Shrivastwa
Rashmi Kant Shrivastwa

Reputation: 1157

use this query 

select *  from product where Rating in (
select max(Rating)  from product group by  ProductId )

and you will get the following result

1   1   9
500 1   7

Upvotes: 0

Surasin Tancharoen
Surasin Tancharoen

Reputation: 5850

I would prefer to have just one SQL like this

select *  
from product p1
where rating = (select max(Rating)  
                from product p2
                where p1.ProductId = p2.ProductId)

However this SQL can return mutiple results of the same product if your data has something like:

IdentityId  ProductId   TypeId   Rating
3           1             1      9
7           1             2      9  // note: same max rating

The result will show both rows.

If you really want to show just 1 row per product and you do not care which typeId will be shown. You can use this SQL instead.

select p1.productId, max(p1.typeId), max(Rating)  
from product p1
where rating = (select max(Rating)  
                from product p2
                where p1.ProductId = p2.ProductId)
group by p1.productId

You can change max(p1.typeId) to min(p1.typeId)

I hope this could be another alternative.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @T table
(
  IdentityId int,
  ProductId int,
  TypeId int,
  Rating int
)

insert into @T values  
(3,           1,             1,      9),
(7,           1,             2,      3),
(9,           500,           1,      7),
(2,           500,           2,      5),
(2,           777,           4,      5),
(12,          777,           3,      8),
(2,           999,           4,      1)

;with C as
(
  select ProductId,
         TypeId,
         Rating,
         row_number() over(partition by ProductID 
                           order by Rating desc) as rn
  from @T
)
select ProductId,
       TypeId,
       Rating
from C
where rn = 1
order by ProductId

Upvotes: 1

Aaron
Aaron

Reputation: 168

try the following: 

  Select [ProductID], [TypeID], [Rating] 
  from [tblTest] 
  WHERE [Rating] in 
  (
    SELECT MAX([Rating])
    from [test].[dbo].[tblTest]
    group by [TypeID] 
  )

Upvotes: 2

Pete855217
Pete855217

Reputation: 1602

select IdentityId, ProductId, max(Rating)  from TABLENAME group by ProductId

This seems to work, although I'm not sure how 'legal' it is to leave identity id off the group by clause. (snap @ Bryan ;)

Upvotes: -1

Related Questions