By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 5.5.62 |
create table product_group_table(product_group_id int, product_id int, product_price int, product_description varchar(155));
insert into product_group_table VALUES(4, 1, 15, 'Loremipsum 1');
insert into product_group_table VALUES(4, 2, 10, 'Loremipsum 2');
insert into product_group_table VALUES(5, 3, 24, 'Loremipsum 3');
insert into product_group_table VALUES(4, 4, 30, 'Loremipsum 4');
select * from product_group_table;
| product_group_id | product_id | product_price | product_description |
|---|---|---|---|
| 4 | 1 | 15 | Loremipsum 1 |
| 4 | 2 | 10 | Loremipsum 2 |
| 5 | 3 | 24 | Loremipsum 3 |
| 4 | 4 | 30 | Loremipsum 4 |
/* Your query */
SELECT
product_group_id,
product_id,
MAX(product_price) AS product_price,
product_description
FROM product_group_table
AS product_group_table
GROUP BY product_group_id;
| product_group_id | product_id | product_price | product_description |
|---|---|---|---|
| 4 | 1 | 30 | Loremipsum 1 |
| 5 | 3 | 24 | Loremipsum 3 |
/* My Query */
SELECT pg.product_group_id, pg.product_id, pg.product_price, pg.product_description
FROM product_group_table AS pg
INNER JOIN
(
SELECT product_group_id, MAX(product_price) AS mx_price
FROM product_group_table
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;
| product_group_id | product_id | product_price | product_description |
|---|---|---|---|
| 4 | 4 | 30 | Loremipsum 4 |
| 5 | 3 | 24 | Loremipsum 3 |
/* This query will not work here because product table does not exist.
It will work perfectly with you. */
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;
Table 'db_745054194.product' doesn't exist