How do I compare 2 most recent prices for the same item in a single row?

user_16683019 0
Viewed 51

So I have a DB that looks like this:

sample DB

It contains prices for items with timestamps. I need to return most recent prices from different shops for the same item along with the difference in a single row, so it looks like this:

desired result

I'm farily new to SQL, and I just couldn't wrap my head around this task. I was able to return the list with all needed info:

select i1.item, i1.shop, i1.last_modified, i1.price
from items i1
inner join(
select item, max(last_modified) as MaxLastModified
from items group by item) groupedi1
on i1.item = groupedi1.item
and i1.last_modified = groupedi1.MaxLastModified;

but to order it in a single row is beyond me for now.

mysql
1 Answers

Can you try this one? It seems you need to pivot the result set. And since there is only comparison between two shops price you can also use case statement. Here is my sample code (not tested) but logic should work.

SELECT 
    diff.item,
    MAX(CASE WHEN diff.shop = '2002' THEN diff.Price END) "Shop1",
    MAX(CASE WHEN diff.shop = '2006' THEN diff.Price END) "Shop2",
    MAX(CASE WHEN diff.shop = '2002' THEN diff.Price END) - MAX(CASE WHEN diff.shop = '2006' THEN diff.Price END) as Difference
FROM
    (select i1.item, i1.shop, i1.price
    from items i1
    inner join(
        select item, shop, max(last_modified) as MaxLastModified
        from items group by item, shop) groupedi1
    on i1.item = groupedi1.item AND i1.shop = groupedi1.shop
       and i1.last_modified = groupedi1.MaxLastModified) diff 
Group By diff.item
user_67481900
Related