get 5 latest invoices (based on InvoiceDate) for each customer.

[thanhphong122]

I have two tables:
- CustomerID(CustID,CustName)
- Invoice(CustID,InvoiceID,InvoiceDate)
How can i get a table that has 5 latest invoices (based on InvoiceDate) for each customer.
Example: The result has format like this:
CustID CustName InvoiceID InvoiceDate
0001 Ng V A 126121 20061028
0001 Ng V A 126128 20061026
0001 Ng V A 126130 20061022
0001 Ng V A 126132 20061019
0001 Ng V A 126140 20061018
0003 Ng V B 126050 20061024
0003 Ng V B 126046 20061016
0003 Ng V B 126038 20061012
0003 Ng V B 126012 20061010
Please help me to use query to solve this problem. Many thanks for your kind support.
Best regards

[Taftheman]

can't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)

[thanhphong122]

can't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.

[Taftheman]

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.

Im not sure but try just joining the two tables.

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 I.InvoiceDate from Invoice I Left Join CustomerID C I.CustId = C.CustID Order By InvoiceDate DESC)

If that doesn't work then put custid in both where clauses

[Taftheman]

You can use this, it gets all the top 5 dates from the customer id of QUICK, change to suit your needs... You can even run it on query analyzer as it uses the northwind database

Select C.CustomerId, C.ContactName, O.OrderDate
from Customers C left join Orders O on C.CustomerId = O.CustomerId
Where C.CustomerId = 'QUICK' and O.OrderDate in (Select top 5 OrderDate from Orders Where CustomerId = 'QUICK' order by OrderDate DESC)

Let us know if it works

[thanhphong122]

The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.

[willakawill]

The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE

[willakawill]

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE

Oops!

ORDER BY i.ILDATE DESC