CODEWITHSUNDEEP
sqlms-accesssubquerydistinct
Ob On Ken
Ob On Ken

Reputation: 35

Use query result as parameter for another query

I have two tables:

table1: item (itemId,itemName)

table2: invoices (InvoiceId,InvoiceNum,InvoiceDate,ItemId..etc) (other columns are not relevant).

I need to find for a distinct item, the latest (and only the latest) invoice date. So far I create a first query distinct on the first table and then, for each result I run a new query on the second table (top 1 ordered by date desc)

It works, but it takes forever (I have more than 10000 items). I am using asp classic and access but I am pretty sure that the problem is related to the SQL.

I tried with the clause WHERE ... IN but the query output all the invoice dates and not only the latest one.

Any help? Thx in advance!

I understand that I've been very generic. here is the SQL instruction that I am trying to use:

SELECT table1.id, table3.name, table1.name, table2.trans_num, Max(table2.transaction_date_h) AS MaxDitransaction_date_h, table2.amount_amt FROM table3 INNER JOIN (table1 INNER JOIN table2 ON table1.id = table2.item_id) ON table3.id = table1.preferred_vendor_id GROUP BY table1.id,table3.name, table1.name, table2.trans_num, table2.amount_amt ORDER BY table3.name, table1.name, table2.trans_num, Max(table2.transaction_date_h) DESC;

If I run this query, the result is the entire recordset. As said, I would like to retrieve only the top 1 latest transaction_date_h for each distinct table1.id

Upvotes: 0

Views: 2037

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

If you just want item Id and latest invoice date, then use simple aggregation:

select itemId,
    max(InvoiceDate) as InvoiceDate
from invoices
group by itemId

If you need item details too, join the above with item table:

select i.*, i2.InvoiceDate
from item i
join (
    select itemId,
        max(InvoiceDate) as InvoiceDate
    from invoices
    group by itemId
    ) i2 on i.itemId = i2.itemId;

or:

select i.itemId,
    i.itemName,
    max(i2.InvoiceDate) as InvoiceDate
from item i
join invoices i2 on i.itemId = i2.itemId
group by i.itemId,
    i.itemName;

Upvotes: 1

Related Questions