CODEWITHSUNDEEP
sqlfirebirdgreatest-n-per-group
Nick Campbell
Nick Campbell

Reputation: 31

firebird sql get corresponding field from max value

I'm working on this report which is meant to return the last purchase date and last purchase quantity for a list of items.

my basic sql is as follows:

select im.ItemName, max(prh.Receiptdate), prl.ReceiptQuantity/*which corresponds with max ReceiptDate*/
from ItemMaster im
left join PurchaseReceiptLines prl on prl.ItemName = im.ItemName
left join PurchaseReceiptHeader prh on prh.ReceiptNum = prl.ReceiptNum
group by im.ItemName

Does anyone have any pointers?

Any help would be awesome

Upvotes: 1

Views: 1906

Answers (3)

Arioch 'The
Arioch 'The

Reputation: 16065

Does your table have a unique synthetic ID - a primary key that is always increased when inserting new row ? Usually there is one. And then the largest IDs would correspond to latest purchases typically (unless you have offline purchases that you insert into your DB long after the fact).So, usually (not always) you can instead look for max(ID) per good, not max(date). This has a bonus that you would not have two rows with the same ID while you can have to rows with the same date.... In the latter case you would unexpectedly get several rows per good and your program would puke

So, from this point on I would assume that those are true: 1) in your tables you have a typical integer PRIMARY-KEY column ID, filled with generator(aka sequence), so that the row inserted later would always have its ID larger. 2) you insert purchase records as those purchases are done, immediately. You do not sell when offline and you do not later batch-insert accumulated data that already is few hours/days old.

actually, your ReceiptNum might be like that id, if you do not reset it to 1 every year or every month. But usually real-world document numbers are getting resetted on timely matter, so the synthetic ID is preferred for linking tables together

In this case getting last records per-good is rather simple:

SELECT MAX(ID), ItemName from PurchaseReceiptLines group by ItemName

granted, this does not give you dates and quantities, because it is a "totalling" request.

SELECT PRL_Data.ID, PRL_Data.ItemName, prh.Receiptdate, PRL_Data.ReceiptQuantity 
FROM PurchaseReceiptLines PRL_Data 
JOIN (SELECT MAX(ID) as ID, ItemName from PurchaseReceiptLines group by ItemName) PRL_Max
  ON PRL_Max.ID = PRL_Data.ID
JOIN PurchaseReceiptHeader prh
  ON prh.ID = PRL_Data.Header_ID  /* like prh.ReceiptNum = prl.ReceiptNum but with never resetting */

Upvotes: 0

Christian
Christian

Reputation: 1

select im.itemname, max(prh.receiptdate), 
(select prl.receiptquantity from purchasereceiptlines prl
   join purchasereceiptheader prh on prh.receiptnum = prl.receiptnum
  where prl.itemname = im.itemname)
from itemmaster im
left join purchasereceiptlines prl on prl.itemname = im.itemname
left join purchasereceiptheader prh on prh.receiptnum = prl.receiptnum
group by im.itemname

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270553

Firebird 3.0 supports window functions. In that case, row_number() is the right approach. In older versions:

with i as (
      select im.ItemName, prh.Receiptdate, prl.ReceiptQuantity
      from ItemMaster im left join
           PurchaseReceiptLines prl
           on prl.ItemName = im.ItemName left join
           PurchaseReceiptHeader prh
           on prh.ReceiptNum = prl.ReceiptNum
     )
select i.*
from i
where i.Receiptdate = (select i2.Receiptdate from i i2 where i2.ItemName = i.ItemName);

Upvotes: 1

Related Questions