CODEWITHSUNDEEP
mysqlsql
user1613843
user1613843

Reputation: 49

Select only the latest version of the data with SQL join

I have two tables one containg offer information and the other containg products something like this:

           OFFER                                 PRODUCTS
ID      Number      Version          poID      offer_id     Product       how_many
==========================            ========================================
1          123       1                1         1              Apple        1
2          123       2                2         1              Banana       2
3          124       1                3         1              Orange       1
                                      4         2              Apple        1
                                      5         2              Banana       2
                                      6         2              Orange       2
                                      7         2              Kiwi         1
                                      8         3              Apple        2
                                      9         3              Banana       3

I would like a list of how many products that are currently offered. Since OFFER(id = 2) is an update of (id = 1) only (id = 2) should be counted.

How should I best query this?

Upvotes: 2

Views: 265

Answers (4)

HLGEM
HLGEM

Reputation: 96570

Try this:

select [list columns here]
from products p
join (select offernumber, max(id) as ID from offer group by offernumber) a
    on a.id = p.offer_id

If you need addtional columns from offer other than the offernumber and the id:

select [list columns here]
from products p
join (select offernumber, max(id) as ID from offer group by offernumber) a
    on a.id = p.offer_id
join offer o on o.id = a.id

Upvotes: 0

StevieG
StevieG

Reputation: 8709

SELECT * 
FROM products
WHERE offer_id = (SELECT MAX(id) FROM offer)

or, if you prefer the join syntax

SELECT p.* 
FROM products p
INNER JOIN (SELECT MAX(id) id FROM offer) o ON p.offer_id = o.id

Edit (still not completely sure this is what you want without seeing your desired results)

SELECT p.* 
FROM products p
INNER JOIN offer o on p.offer_id = o.id
INNER JOIN 
(SELECT number, max(version) 
 FROM offer
 GROUP BY number
) oMax ON o.number = oMax.number AND o.version = oMax.version

Upvotes: 0

user330315
user330315

Reputation:

First you need to get all the latests offers:

select o.id
from offer o
where version = (select max(version)
                 from offer o2
                 where o2.number = o.number);

Based on the above you can then get all the products:

select p.*
from products p
where offer_id in (select o.id
                   from offer o
                   where version = (select max(version)
                                    from offer o2
                                    where o2.number = o.number));

Upvotes: 1

Chris
Chris

Reputation: 8109

If id and version correlate:

select sum(how_many) from products p
join offer on p.offer_id=offer.id
join (
    select number, max(version) version from offer group by number  
) x 
on offer.id=x.id and offer.version = x.version

Upvotes: 0

Related Questions