Select rows with lowest price grouped by another column

Question

I have this table.

I want to select rows with lowest price of each pg_id

I have this query

SELECT
    *, MIN(product_price)
FROM
    `product_group_scrape_history`
GROUP BY
    pg_id;

But its giving me this result

Notice the date_scraped column of 1st row. Its 2017-03-01 09:15:17 but it should be 2017-03-09 06:25:41.

Answer 1

If you wan to find row with min price in a pg_id, you can find the min price per pg_id in a subquery and then join it with the table to get the desired result.

select p1.*
from product_group_scrape_history p1
join (
    select pg_id,
        min(product_price) product_price
    from product_group_scrape_history
    group by pg_id
    ) p2 on p1.pg_id = p2.pg_id
    and p1.product_price = p2.product_price

Note that this will return multiple row per pg_id in case there are multiple rows with min product price for that pg_id.

If you really need one row per pg_id with min product price, you can use user variables:

select *
from (
    select
        t.*,
        @rn := if(@pg_id = pg_id, @rn + 1, if(@pg_id := pg_id, 1, 1)) rn
    from (
        select *
        from product_group_scrape_history
        order by pg_id, product_price
    ) t, (select @rn := 0, @pg_id := -1) t2
) t where rn = 1;

If you meant to find rows with min price for each product in a pg_id, you can use:

select p1.*
from product_group_scrape_history p1
join (
    select pg_id,
        product_id,
        min(product_price) product_price
    from product_group_scrape_history
    group by pg_id,
        product_id
    ) p2 on p1.pg_id = p2.pg_id
    and p1.product_price = p2.product_price
    and p1.product_id = p2.product_id;