This is a simple SQL select query to illustrate how to
select top (or bottom) N from each group.
Here is the data structure
Table 1: product
Prodcut_ID
|
Product_Name
|
1
|
Apple
|
2
|
Orange
|
3
|
Banana
|
4
|
Mango
|
Table 2: Review
Review_ID
|
Product_FK
|
Score
|
1
|
1
|
9
|
2
|
4
|
3
|
3
|
3
|
8
|
4
|
2
|
9
|
5
|
2
|
7
|
6
|
3
|
6
|
7
|
2
|
8
|
8
|
1
|
6
|
9
|
2
|
6
|
10
|
3
|
7
|
11
|
4
|
8
|
12
|
4
|
5
|
SQL query to select top review score from each product
SELECT P.Product_Name, R.Score AS 'Top Score' FROM
Product AS P
LEFT JOIN Review AS R ON R.Product_FK = P.Prodcut_ID
AND R.Review_ID =
(SELECT TOP 1 R2.Review_ID FROM Review AS R2
WHERE R2.Product_FK = P.Prodcut_ID
ORDER BY R2.Score DESC)
Here is the select result:
Product_Name
|
Top Score
|
Apple
|
9
|
Orange
|
9
|
Banana
|
8
|
Mango
|
8
|
You can also use the same query to get average or do other calculations
