CODEWITHSUNDEEP
mysqlsql
Danedo
Danedo

Reputation: 2263

Selecting by MAX(date) from joined table

I am running this query.

SELECT 
  t1.quantity, 
  t2.transaction_date, 
  t1.product_id 
FROM 
  ezsystem_usah.invoice_line AS t1     
  INNER JOIN ezsystem_usah.invoices AS t2 
    ON t1.invoice_id = t2.id_invoices 
WHERE 
  t2.customer_id = '8000004C-1325619329' 
  AND ( product_id = '8000016F-1325198704' 
     OR product_id = '80000027-1324422404' OR ...);

The resulting table is:

quantity | transaction_date | product_id
7          2012-01-04         8000016F-1325198704
8          2012-03-05         8000016F-1325198704
1          2012-01-05         11111111-1324422404
...        ...                ...

I would like to select rows based upon MAX(transaction_date) for a particular product_id, in order to yield something like this:

quantity | transaction_date | product_id
8          2012-03-05         8000016F-1325198704
1          2012-01-05         11111111-1324422404

In other words, from the joined table, I want to select the latest entry of EACH product id.

I have tried:

SELECT 
  t1.quantity, 
  MAX(t2.transaction_date), 
  t1.product_id 
FROM 
  ezsystem_usah.invoice_line AS t1 
  INNER JOIN ezsystem_usah.invoices AS t2 ON t1.invoice_id = t2.id_invoices 
WHERE 
  t2.customer_id = '8000004C-1325619329' 
  AND (product_id = '8000016F-1325198704' OR product_id = '80000027-1324422404' OR ...)     
GROUP BY 
 t1.product_id;

However the quantity is incorrect.

Another way I haven't tried but I think might work is to use HAVING MAX(transaction_date) = ([sub query]), but it seems costly.

Worst case, I can run queries for each product one by one, but I'd like to avoid that if possible.

Thanks, Dane

Upvotes: 2

Views: 993

Answers (4)

Marcus Adams
Marcus Adams

Reputation: 53830

Your alternatives are sub queries (as provided by in other answers), or the following anti-join (self-join):

SELECT 
  t1.quantity, 
  t2.transaction_date, 
  t1.product_id 
FROM 
  ezsystem_usah.invoice_line AS t1 
  INNER JOIN ezsystem_usah.invoices AS t2
    ON t1.invoice_id = t2.id_invoices 
  LEFT JOIN ezsystem_usah.invoices AS t3
    ON t3.transaction_date > t2.transaction_date
    AND t3.id_invoices = t2.id_invoices
WHERE 
  t2.customer_id = '8000004C-1325619329' AND
  (t1.product_id = '8000016F-1325198704' OR t1.product_id = '80000027-1324422404' OR ...) AND
  t3.id_invoices IS NULL
GROUP BY 
t1.product_id

The idea is that if invoices with dates greater than the current row are found, it excludes the row. I may not have the extra join exactly right. I'm making some guesses about the primary key.

Upvotes: 0

SetFreeByTruth
SetFreeByTruth

Reputation: 847

Does this do the trick?

SELECT 
  t1.quantity, 
  transaction_date,
  product_id
FROM 
  ezsystem_usah.invoice_line AS t1 
  INNER JOIN ezsystem_usah.invoices AS t2 ON t1.invoice_id = t2.id_invoices 
WHERE 
  t2.customer_id = '8000004C-1325619329' 
  AND product_id IN ( '8000016F-1325198704', '80000027-1324422404' )   
  AND transaction_date = ( SELECT MAX( inv.transaction_date ) 
                           FROM ezsystem_usah.invoices inv 
                             INNER JOIN ezsystem_usah.invoice_line ln 
                               ON    inv.customer_id = t2.customer_id
                                 AND ln.invoice_id = inv.id_invoices 
                                 AND ln.product_id = t1.product_id
                         )

I took a guess which tables contain the fields product_id and transaction_date as it's not clear from the original query.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Here is a relatively simple formulation

with t as (<your query here>)
select t.*
from t join
     (select t.product_id, max(t.transaction_date) as maxdate
      from t
      group by t.product_id
     ) tmax
     on t.product_id = tmax.product_id and
        t.transaction_date = tmax.maxdate

This solution only requires you to include your query once, using the "with" clause.

Upvotes: 0

LSerni
LSerni

Reputation: 57388

This should do what you ask for and be reasonably efficient. Products are checked with IN () in the inner loop, so if you have indexing on product_id and customer_id it should run pretty fast. Indexing on t1.invoice_id is also recommended.

SELECT 
  t1.quantity, 
  transaction_date, 
  product_id 
FROM 
  ezsystem_usah.invoice_line AS t1     
  JOIN (
    SELECT t3.transaction_date, t3.product_id, t3.id_invoices 
        FROM ezsystem_usah.invoices t3
        JOIN
( SELECT MAX(transaction_date) AS max_transaction_date,
    product_id
    FROM ezsystem_usah.invoices
    WHERE product_id IN (
      '8000016F-1325198704', '80000027-1324422404'
      )
    AND customer_id = '8000004C-1325619329'
    GROUP BY product_id ) AS uniqued
    ON ( t3.transaction_date = uniqued.max_transaction_date
        AND t3.product_id = uniqued.product_id
    )
) AS t2 
ON t1.invoice_id = t2.id_invoices;

Upvotes: 2

Related Questions