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?

Answers


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));

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

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

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 

Need Your Help

System.Transactions.Diagnostics.DiagnosticTrace throwing TypeInitializationException

.net .net-4.0 c++-cli windows-8

Seems related to Strange exception coming out of OdbcConnection.Open() but I'm not sure.

Login in C# using stored procedure from MySQL

c# mysql wpf

I am making a userform that will login to database server first and then authenticate from one of the tables which has user details.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.