Return only latest entry in Database query containg multiple linked tables

Question

I am seriously out of my depth on this one... I have a MS Query with 3 linked tables returning multiple results. I need to limit them to the most recent entry because the data is too large to import into Excel as it stands. Here is a copy of the SQL that is currently being used. I need to eliminate all but the most recent entry based on the field inventory.lastissuedate

SELECT invbalances.itemnum
     , invbalances.curbal
     , inventory.maxlevel
     , a_inventory52.minlevel
     , inventory.sstock
     , inventory.deliverytime
     , inventory.category
     , inventory.lastissuedate
FROM MX7PROD.dbo.a_inventory52 a_inventory52
    , MX7PROD.dbo.invbalances invbalances, MX7PROD.dbo.inventory inventory
WHERE invbalances.itemnum = inventory.itemnum 
AND a_inventory52.itemnum = invbalances.itemnum 
AND ((inventory.category<>'cstk' 
And inventory.category<>'ns' 
And inventory.category<>'nore' 
And inventory.category<>'sp'))

Most recent as determined by the latest/highest value of what field? — Brian DeMilia, Feb 22 '14 at 16:54

score 0 · Accepted Answer · answered Feb 22 '14 at 16:55

0

Try adding this at the end:

AND inventory.lastissuedate = 
(SELECT MAX(lastissuedate) FROM MX7PROD.dbo.inventory)

answered Feb 22 '14 at 16:55

jpw

44,361
6
66
86

This worked. Thank you. Now I'm having another issue where there are entries with the exact lastissuedate that don't match the inventory receipts. I'm trying to find another unique field to eliminate these. – user3341199 Feb 22 '14 at 17:00
@user3341199 Happy to help. Please consider accepting and/or voting up my answer if it solved your problem. Also, if you have another question it's better to post that as a new question instead of in a comment so it receives attention. – jpw Feb 22 '14 at 17:02
I've managed to narrow down the result set, but it turns out to be much more complicated than I thought. I need the most recent curbal for each itemnum in each location. By using the lastissuedate i actually only get the most recent transaction in the entire stock. Any more advice? I'll post the current code. – user3341199 Feb 22 '14 at 17:33
SELECT invbalances.itemnum, invbalances.itemnum, invbalances.curbal, inventory.maxlevel, inventory.minlevel2, inventory.sstock, inventory.deliverytime, invbalances.location, inventory.category, inventory.lastissuedate, invbalances.invbalancesid FROM MX7PROD.dbo.invbalances invbalances, MX7PROD.dbo.inventory inventory WHERE invbalances.itemnum = inventory.itemnum AND invbalances.location = inventory.location AND ((inventory.category<>'cstk' And inventory.category<>'nore' And inventory.category<>'sp') AND (invbalances.itemnum Not Like '%n%')) ORDER BY invbalances.itemnum – user3341199 Feb 22 '14 at 17:34
@user3341199 You can accomplish this with a correlated subquery. It would look something like `select * from mytable inner join (select itemnum, location, max(lastissuedate) from mytable) table_alias on mytable.itemnum=table_alias.itemnum and mytable.location=table_alias.location and so on... – jpw Feb 22 '14 at 17:39
@user3341199 But as I said before, it's really better to post a new question as trying to answer in comments gets tedious fast :) – jpw Feb 22 '14 at 17:40
I can't chat. I'm new. – user3341199 Feb 22 '14 at 17:41
SELECT invbalances.itemnum, invbalances.itemnum, invbalances.curbal, inventory.maxlevel, inventory.minlevel2, inventory.sstock, inventory.deliverytime, invbalances.location, inventory.category, inventory.lastissuedate, invbalances.invbalancesid FROM MX7PROD.dbo.invbalances invbalances, MX7PROD.dbo.inventory inventory WHERE invbalances.itemnum = inventory.itemnum AND invbalances.location = inventory.location AND ((inventory.category<>'cstk' And inventory.category<>'nore' And inventory.category<>'sp') AND (invbalances.itemnum Not Like '%n%')) ORDER BY invbalances.itemnum – user3341199 Feb 22 '14 at 17:41
What table has the location? – Brian DeMilia Feb 22 '14 at 17:48
Sorry, I got called away. invbalances has the location. Thank you all for the help. – user3341199 Feb 22 '14 at 18:16

score 0 · Answer 2 · answered Feb 22 '14 at 17:50

Edited to use table invbalances.location for location field.

This will limit your data to rows where the curbal is the last curbal for the given item and location.

SELECT invbalances.itemnum,
       invbalances.curbal,
       inventory.maxlevel,
       a_inventory52.minlevel,
       inventory.sstock,
       inventory.deliverytime,
       inventory.category,
       inventory.lastissuedate
  FROM MX7PROD.dbo.a_inventory52 a_inventory52,
       MX7PROD.dbo.invbalances   invbalances,
       MX7PROD.dbo.inventory     inventory
 WHERE invbalances.itemnum = inventory.itemnum
   AND a_inventory52.itemnum = invbalances.itemnum
   AND inventory.category <> 'cstk'
   And inventory.category <> 'ns'
   And inventory.category <> 'nore'
   And inventory.category <> 'sp'
   and invbalances.curbal =
       (SELECT MAX(x.curbal)
          FROM MX7PROD.dbo.inventory x
         where x.itemnum = invbalances.itemnum
           and x.location = invbalances.location)

Edited for the last curbal. This is why I asked what field determined the "most recent" record by the way. There is no way for us to know unless you tell us. — Brian DeMilia, Feb 22 '14 at 17:52

Return only latest entry in Database query containg multiple linked tables

2 Answers2