hi, i have a company table that is dbo.companies and has companyId as a column. I also have an invoice table that is dbo.invoices with invoicecompanyId column (which is the same as the companyId on the other table) and it also has a column called invoicedate. What i am mtrying to achieve is a view of each companyid with the corresponding latest invoice date for all the companies i have.

i have done the following but i dont know how to filter for the latest invoice, it returns all invoices from all companies and i need latest invoice for all companies

SELECT     TOP (100) PERCENT 
    'A' + 
    SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS varchar(10)))) +
    CAST(dbo.companies.companyId AS varchar(10)) AS Client_ID, 
    dbo.invoices.invoiceDate AS S_Inv_Date
FROM dbo.invoices 
INNER JOIN dbo.companies 
ON dbo.invoices.invoiceCompanyId = dbo.companies.companyId
ORDER BY Client_ID

can you help please

ta