CODEWITHSUNDEEP
mysqlsqldatabasems-accessgreatest-n-per-group
matiszac
matiszac

Reputation: 125

Retrieving most recent record per id. How to, in SQL?

I have two tables. One table stores basic information on inventory items like id and description. The other table stores the status of the inventory items at the end of each day; so there are many rows in this table with the same inventoryItemID but different dates. Below is basically what my tables look like.

TABLE: listItems
|itemRecordNumber|itemName|minStock|reorderQty|note|
|      510       |  bag   |   10   |    20    |    |
|      511       |  ball  |   20   |    40    |    |
|      512       |  shoe  |   09   |    10    | xx |

,

TABLE: inventoryTrans
|itemRecordNumber|recordType|quantity| trans_date |
|      510       |    50    |   30   | 09/12/2013 |
|      510       |    40    |   33   | 09/12/2013 |
|      510       |    50    |   35   | 08/12/2013 |
|      510       |    40    |   35   | 08/12/2013 |
|      511       |    50    |   10   | 08/12/2013 |
|      511       |    40    |   15   | 09/12/2013 |
|      512       |    50    |   33   | 07/12/2013 |
|      512       |    40    |   34   | 09/12/2013 |

Now, what i want is, the most recent record from the table inventoryTrans for each itemRecordNumber and only the records of type 50(a sale), but wait theres more, if the note in the table listItems on an item is 'xx' i am not interested in that result. What i really need from this table is the quantity. Notice this table does not have the items name / minStock / reorderQty , which is why i need the first table listItems, to get that info.

So in a single query i would like to get,

|itemRecordNumber|itemName| trans_date |quantity|minStock|reorderQty|
|      510       |  bag   | 09/12/2013 |   30   |   10   |    20    |
|      511       |  ball  | 08/12/2013 |   10   |   20   |    40    |

So what i want is this information. Notice it is the most recent of the recordType '50' for each ID, yet item 512 was excluded due to the note on the item being 'xx' in listItems.note ;

Now before you criticize the construct of the tables, it is not something I can change. This is how the tables for Peachtree accounting(the software my company at hand is using) is layed out, so this is what I have to work with. The goal is to generate custom information on inventory for better management.

Now how can I construct a SQL query for this. i have seen people use LEFT JOIN in some other similar examples but i cant get it to work.

Upvotes: 1

Views: 384

Answers (2)

skv
skv

Reputation: 1803

Here we go

http://sqlfiddle.com/#!2/d30823/1 this is the example with your data 

SELECT a.itemRecordNumber,
       a.itemName,
       max(b.trans_date) AS trans_date,
       b.quantity,
       a.minStock,
       a.reorderQty
FROM listItems a,
     inventoryTrans b
WHERE a.itemRecordNumber = b.itemRecordNumber
  AND a.note<>'xx'
  AND b.recordtype=50
GROUP BY a.itemRecordNumber

Edit: In case of MS Access (which I am unable to test), this should work, can you try (I did try in MS SQL which is the closest I can test in SQLFiddle

http://sqlfiddle.com/#!3/d30823/2

SELECT a.itemRecordNumber,
       a.itemName,
       max(b.trans_date) AS trans_date,
       b.quantity,
       a.minStock,
       a.reorderQty
FROM listItems a,
     inventoryTrans b
WHERE a.itemRecordNumber = b.itemRecordNumber
  AND a.note<>'xx'
  AND b.recordtype=50
  AND b.trans_date =
    (SELECT max(trans_date)
     FROM inventoryTrans c
     WHERE c.itemRecordNumber = b.itemRecordNumber
       AND c.recordtype=50)
GROUP BY a.itemRecordNumber,
         a.itemName,
         b.quantity,
         a.minStock,
         a.reorderQty

Let me know if it does not work for you for any reason, I got the same results as your question

Upvotes: 1

user2989408
user2989408

Reputation: 3137

Though this is not the most efficient way, it should work.

SELECT l.*, i.trans_date, i.quantity
FROM listItems as l
    JOIN (SELECT i.itemRecordNumber, MAX(trans_date) as date
          FROM inventoryTrans as i GROUP BY i.itemRecordNumber
     ) as x ON x.itemRecordNumber = l.itemRecordNumber
    JOIN inventoryTrans as i ON l.itemRecordNumber = i.itemRecordNumber
        AND i.trans_date = x.date
WHERE i.recordType = 50

Though this will give a row for itemRecordNumber = 512 too, if you do not need it you can add a WHERE and filter according to your need.

Upvotes: 0

Related Questions