Failing to display joining key on SQL Joins

Question

I have standard 2 tables - I want to display highest selling products (by Sale) in each Group

Product_Info_Table -> 
Product_Id, Product_Group, Product_Name
P1  BOOK    3 Musketeers
P10 SHOES   NIKE Fly
P11 SHOES   NIKE Thunder
P12 SHOES   ADIDAS 10
P13 SHOES   ADIDAS 09
P14 BOOK    Harry Potter 1
P15 BOOK    Harry Potter 2
P2  BOOK    HARRY POTTER 7
P3  ELECTRONICS NIKON 5600i
P4  ELECTRONICS CANNON 4d
P5  ELECTRONICS CANNON 5d
P6  VIDEO DVD   LOR 1
P7  VIDEO DVD   LOR 2
P8  VIDEO DVD   HP 1
P9  VIDEO DVD   HP 2

And Product_Sales_Table

Product_ID SALES_AMT
P1  10
P10 8
P11 45
P2  5
P3  5
P4  25
P5  15
P6  35
P7  5
P8  100
P9  30

I did the following:

SELECT product_info_table.PRODUCT_GROUP, 
product_info_table.PRODUCT_ID,
MAX(IFNULL(sales_fact_table.SALES_AMT,0))

FROM product_info_table
INNER JOIN product_sales_table 
ON product_sales_table.PRODUCT_ID = product_info_table.PRODUCT_ID

GROUP BY product_info_table.PRODUCT_GROUP

Result:

Product_Group Id    Max_Sales
BOOK          P1    10
SHOES         P10   45
ELECTRONICS   P3    25
VIDEO DVD     P6    100

Error: Strangely, this result correctly identifies the max selling products in each category however, the Product_Id is not shown correctly, by default the 1st product_id is picked up in each category.

What am I doing wrong to display the correct product_id.

Desired_Result

Product_Group Id    Max_Sales
BOOK          P1    10
SHOES         P11   45
ELECTRONICS   P4    25
VIDEO DVD     P8    100

Answer 1

Use window functions not aggregation:

SELECT p.*
FROM (SELECT pit.PRODUCT_GROUP, pit.PRODUCT_ID, pst.SALES_AMT,
             ROW_NUMBER() OVER (PARTITION BY pit.PRODUCT_GROUP ORDER BY pst.SALES_AMT DESC) as seqnum
      FROM product_info_table pit JOIN
           product_sales_table pst
           ON pst.PRODUCT_ID = pit.PRODUCT_ID
     ) p
WHERE seqnum = 1;