I have this query that I want to modify further to get the vendor of the last purchase order. Currently, I am getting 2 results for one item because vendors are different. How can I just get the vendor from the last purchase order?

SELECT
    TransactionLine.Item,
    BUILTIN.DF( TransactionLine.Item ) AS ItemID,
    MAX( TranDate ) AS LastPurchasedDate,
    BUILTIN.DF(Transaction.entity) AS Name
FROM
    Transaction
INNER JOIN TransactionLine ON
    ( TransactionLine.Transaction = Transaction.ID )
WHERE
    ( Transaction.Type = 'PurchOrd' ) AND
    TransactionLine.Item IN (${itemId.toString()})
GROUP BY
    TransactionLine.Item,
    BUILTIN.DF(Transaction.entity),
    BUILTIN.DF( TransactionLine.Item)

I am not that good with the SQL so any help is appreciated.

Were there two POs on the same, most recent, date, each from a different vendor?

The dates are a bit far, May and Sept.

Gotcha, because you're grouping by item and vendor

yeah, I need item to map the vendor against it. If you have a better query, let me know.

Looks like a job for DENSE_RANK - do you have any experience with that?

I don't

I don't have a lot of experience with it, and I'm sure a SuiteQL guru would run rings around this. Caveat aside, here's one possible implementation. Here, DENSE_RANK "ranks" lines by newest to oldest, per item. Wrapping that query and pulling just the DENSE_RANK = 1 gives us just the most recent one for the item without grouping it with the entity. One more caveat: I've just run this in my environment to make sure it didn't error, but without your second WHERE clause including the itemId. Validation is highly suggested 😄

Thank you! It worked for the most part except for 1 result. It is returning 2 results but both of them are same - not sure why. This one has different vendors but other items do too and they are returned only once. I have used SELECT DISTINCT for now.

On the duplicate - does the same item appear on multiple lines on the same transaction?

I don't think so because Vendors are different and they also have different dates (on results without dense rank)

Oh, sorry, I overlooked the part where you mentioned the different vendors

Thanks. I will try that. I appreciate your help. Any resource to learn SQL?

Google 😄 I like Ask TOM for Oracle SQL in general, and Tim Dietrich is a great resource for SuiteQL-specific knowledge. As for courses, I don't know.

I would do it with

MAX(entity) KEEP (DENSE_RANK LAST ORDER BY trandate)

. Like this:

Thanks @David B, saving this approach in my notes

Don't thank me, thank this guy

Thanks @David B and @Clay Roper