Using this query I can produce the following results:

SELECT product_group_id, product_id, product_price, product_description
    FROM product_group_table
    JOIN product ON product.id = product_id;

The output I want is the following:

Where I get the details of the product with the highest price per group. I tried it with the query:

SELECT product_group_id, product_id, MAX(product_price) AS product_price, product_description
FROM (SELECT product_group_id, product_id, product_price, product_description
    FROM product_group_table
    JOIN product ON product.id = product_id) 
    AS product_group_table
    GROUP BY product_group_id;

But it produces the following result.

CodePudding user response：

One other method to achieve this is by using SUB-QUERY and JOINS. This method will work with most of the oldest versions of MySQL as well:

SELECT pg.product_group_id, pg.product_id, pg.product_price, pg.product_description
FROM 
(
    SELECT product_group_id, product_id, product_price, product_description
    FROM product_group_table
    JOIN product ON product.id = product_id
)AS pg
INNER JOIN 
(
  SELECT product_group_id, MAX(product_price) AS mx_price
  FROM 
  (
    SELECT product_group_id, product_id, product_price, product_description
    FROM product_group_table
    JOIN product ON product.id = product_id
  ) sq
  GROUP BY product_group_id
) AS mx
    ON pg.product_group_id = mx.product_group_id
    AND pg.product_price = mx.mx_price
ORDER BY pg.product_group_id;

Demo on db<>fiddle here

CodePudding user response：

One canonical approach, assuming you use MySQL 8 , uses ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY pgt.product_group_id
                                 ORDER BY p.product_price DESC) rn
    FROM product_group_table pgt
    INNER JOIN product p ON pgt.product_id = p.id
)

SELECT product_group_id, product_id, product_price, product_description
FROM cte
WHERE rn = 1;

CodePudding user response：

I am using MySql Version 5. This problem can be solved by casting from one datatype to another. The query is as follows. It is working. I am assuming that the table name is PRODUCT.

SELECT      A.PRODUCT_GROUP_ID,A.PRODUCT_ID,A.PRODUCT_PRICE,A.PRODUCT_DESCRIPTION
FROM        PRODUCT A
WHERE       CONCAT(TRIM(CAST(A.PRODUCT_GROUP_ID AS CHAR(10))),'-',TRIM(CAST(A.PRODUCT_PRICE AS CHAR(10)))) IN
(
        SELECT      CONCAT(TRIM(CAST(B.PRODUCT_GROUP_ID AS CHAR(10))),'-',TRIM(CAST(MAX(B.PRODUCT_PRICE) AS CHAR(10)))) 
        FROM        PRODUCT B
        GROUP BY    B.PRODUCT_GROUP_ID
)
ORDER BY    A.PRODUCT_GROUP_ID,A.PRODUCT_ID;