Define two or more conditions into a stored procedure with inner join?

Question

First, I needed to filter the data from the tables TbTaxCompanies and tbCompany, through the variable @company_id... and works!

CREATE PROCEDURE taxcompany_importTaxes
    @company_id int
AS
    SELECT 
        tc.idtaxcompany, tc.company_id, c.nameCompany, 
        ct.tax1, ct.tax2, ct.tax3, ct.dateUpgrade
    FROM 
        tbTaxCompanies tc
    INNER JOIN 
        tbCompany c ON tc.company_id = c.idcompany
    WHERE 
        tc.company_id = @company_id
    ORDER BY 
        tc.idtaxcompany ASC

And second I need to filter again the set of logs from the company selected previously with @company_id, but now to obtain only the latest updated taxes (only one row)... and this DOES NOT WORK!

CREATE PROCEDURE taxcompany_importTaxes
    @company_id int
AS
    SELECT 
        tc.idtaxcompany, tc.company_id, c.nameCompany, 
        ct.tax1, ct.tax2, ct.tax3, ct.dateUpdate
    FROM 
        tbTaxCompanies tc
    INNER JOIN 
        tbCompany c ON tc.company_id = c.idcompany
    WHERE 
        tc.company_id = @company_id 
        AND (tc.dateUpdate = (SELECT MAX (dateUpdate) FROM tbTaxCompanies))
    ORDER BY 
        tc.idtaxcompany ASC

I really appreciate any help.

Answer 1

If you want the latest row from the first resultset, just use order by and top:

SELECT TOP (1) WITH TIES
    tc.idtaxcompany, tc.company_id, c.nameCompany, 
    tc.tax1, tc.tax2, tc.tax3, tc.dateUpgrade
FROM tbTaxCompanies tc
INNER JOIN tbCompany c on tc.company_id = c.idcompany
WHERE tc.company_id = @company_id
ORDER BY tc.dateUpdate DESC

If you want the latest row per group, for example per tc.idtaxcompany, then you can just change the ORDER BY clause to:

ORDER BY RANK() OVER(PARTITION BY tc.idtaxcompany ORDER BY tc.dateUpdate DESC)