Clovis Ribeiro Clovis Ribeiro - 4 months ago 10
SQL Question

filter the most recent line, basing on the date

Hi i am using this query to select article belonging to a entity based on the most recent date:

SELECT DISTINCT ld.artigo,
cd.data,
cd.entidade,
cd.tipodoc,
cd.numdoc
FROM cabecdoc AS cd
INNER JOIN linhasdoc AS ld
ON cd.id = ld.idcabecdoc
INNER JOIN cabecdocstatus AS cds
ON ld.idcabecdoc = cds.idcabecdoc
WHERE cd.tipodoc = 'FAR'
AND ld.artigo IS NOT NULL
AND cds.estado <> 't;r'
ORDER BY cd.data DESC


Let's asume that i have this result

Artigo Entidade Data
Tr01 002 10-07-2015
Tr01 003 9-10-2015
Mn09 001 11-12-2016
Jk90 009 12-07-2016
Tr01 012 4-09-2016


The result i am looking for is this:

Artigo entidade data
Mn09 0001 11-12-2016
Jk90 0009 2-07-2016
Tr01 0012 4-09-2016


Thank you very much!

Answer

You can use a windowed ROW_NUMBER() function with a PARTITION on the artigo to determine which one is the most recent, and only pull those results.

;With Cte As
(
    SELECT ld.artigo
        ,cd.data
        ,cd.entidade
        ,cd.tipodoc
        ,cd.numdoc
        ,Row_Number() Over (Partition By ld.artigo Order By cd.data desc) As Seq
    FROM CabecDoc AS cd
    INNER JOIN LinhasDoc AS ld ON cd.id = ld.IdCabecDoc
    INNER JOIN CabecDocStatus AS cds ON ld.IdCabecDoc = cds.IdCabecDoc
    WHERE cd.TipoDoc = 'FAR'
        AND ld.Artigo IS NOT NULL
        AND cds.Estado <> 't;r'
)
Select  artigo, entidade, data
From    Cte
Where   Seq = 1
Comments