CODEWITHSUNDEEP
mysqlsql
Chris Marin
Chris Marin

Reputation: 1

Using MAX command in returning last time product was ordered. SQL

At the moment I have the below result set.

StockMovementDate       ProductId    TransactionReference
2014-01-09 00:00:03     1324         SO56777
2014-01-09 00:00:02     1324         PSA12556
2014-01-09 00:00:01     1324         NSA637
2014-01-06 00:00:01     1325         SO56774
2014-01-05 00:00:03     1324         NSA623
2014-01-05 00:00:02     1326         NSA622
2014-01-05 00:00:01     1326         SO56771

How would I go about receiving the just the most recent StockMovementDate, but also with the relevant ProductId and TransactionReference?

StockMovementDate       ProductId    TransactionReference
2014-01-09 00:00:03     1324         SO56777
2014-01-06 00:00:01     1325         SO56774
2014-01-05 00:00:02     1326         NSA622

If I use MAX then it makes no difference, as with the TransactionReference being unique it still brings back the entire list.

select 
transactionheader.TransactionDate as StockMovementDate, 
product.id as ProductId,
transactionheader.reference 
from 
transactionheader left outer join
transactionline on transactionline.transactionheaderid = transactionheader.id left outer join
product on transactionline.productid = product.id

Upvotes: 0

Views: 41

Answers (3)

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

Try to use this:-

SELECT MAX(A.TransactionDate) AS StockMovementDate, B.ProductId, A.reference as TransactionReference
FROM transactionheader TH left outer join transactionline TO
on TO.transactionheaderid = TH.id 
left outer join product PR on TO.productid = PR.id
GROUP BY PR.ProductId, TH.reference;

Upvotes: 0

Kritner
Kritner

Reputation: 13765

Could do it this way:

SELECT 
    maxTable.transactionDate
    thOuter.productId, 
    thOuter.reference as TransactionReference
FROM transactionHeader thOuter
INNER JOIN (
    SELECT product.id, max(th.transactionDate) as transactionDate
    FROM transactionHeader th
    INNER JOIN product p on tl.productId = p.id
) maxTable ON thOuter.productId = maxTable.Id

Basically the inner join (subquery) keeps track of the maximum date, then you take your base table and join it on that subquery result set to get the data you want. Note that if you have multiple max dates that are equal for the same product, you will get numerous rows meeting the MAX(transactionDate) for the subquery, and this could impact the results you might expect.

Upvotes: 0

Lamak
Lamak

Reputation: 70668

This is one way:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT ProductId, MAX(StockMovementDate) StockMovementDate
            FROM YourTable
            GROUP BY ProductId) B
    ON A.ProductId = B.ProductId
    AND A.StockMovementDate = B.StockMovementDate

Upvotes: 1

Related Questions