CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

returning only the most recent record in dataset

I have a query returning data for several dates.

I want to return only record with the most recent date in the field SAPOD (the date is in fact CYYMMDD where C=0 before 2000 and 1 after 2000 and I can show this as YYYYMMDD using SAPOD=Case when LEFT(SAPOD,1)=1 then '20' else '19' end + SUBSTRING(cast(sapod as nvarchar(7)),2,7))

here is my query:

SELECT GFCUS, Ne.NEEAN, SCDLE, SAPOD, SATCD,CUS.GFCUN, BGCFN1, 
    BGCFN2, BGCFN3, SV.SVCSA, SV.SVNA1, SV.SVNA2, SV.SVNA3, 
     SV.SVNA4, SV.SVNA5, SV.SVPZIP, SV.NONUK
FROM SCPF ACC 
    INNER JOIN GFPF CUS ON GFCPNC = SCAN
    LEFT OUTER JOIN SXPF SEQ ON SXCUS = GFCUS AND SXPRIM = ''
    LEFT OUTER JOIN SVPFClean SV ON SVSEQ = SXSEQ
    LEFT OUTER JOIN BGPF ON BGCUS = GFCUS AND BGCLC = GFCLC
    LEFT OUTER JOIN NEPF NE ON SCAB=NE.NEAB and SCAN=ne.NEAN and SCAS=ne.NEAS
    LEFT OUTER JOIN SAPF SA ON SCAB=SAAB and SCAN=SAAN and SCAS=SAAS
WHERE 
    (SATCD>500 and
    scsac='IV' and
    scbal = 0 and
    scai30<>'Y' and
    scai14<>'Y' and
    not exists(select * from v5pf where v5and=scan and v5bal<>0))
GROUP BY GFCUS, Ne.NEEAN, SCDLE, SAPOD, SATCD,
    CUS.GFCUN, BGCFN1, BGCFN2, BGCFN3, SV.SVCSA, 
     SV.SVNA1, SV.SVNA2, SV.SVNA3, SV.SVNA4, SV.SVNA5, SV.SVPZIP, SV.NONUK
ORDER BY MAX(SCAN) ASC, SAPOD DESC

I am getting results like the below where there are several transactions by a customer, and we only want to show the data of the most recent transaction:

picture of results in Excel

So how can I show just the most recent transaction? Is this a case where I should use an OUTER APPLY or CROSS APPY?

EDIT:

Sorry I should clarify that I need the most recent date for each of the unique records in the field NEEAN which is the Account number

Upvotes: 0

Views: 131

Answers (2)

Chris Pickford
Chris Pickford

Reputation: 8991

You can use ROW_NUMBER() as follows:

SELECT
    ROW_NUMBER() OVER (PARTITION BY Ne.NEEAN ORDER BY SAPOD DESC) AS [Row],
    GFCUS, Ne.NEEAN, SCDLE, SAPOD, SATCD,CUS.GFCUN, BGCFN1, 
    BGCFN2, BGCFN3, SV.SVCSA, SV.SVNA1, SV.SVNA2, SV.SVNA3, 
    SV.SVNA4, SV.SVNA5, SV.SVPZIP, SV.NONUK
FROM SCPF ACC 
    INNER JOIN GFPF CUS ON GFCPNC = SCAN
    LEFT OUTER JOIN SXPF SEQ ON SXCUS = GFCUS AND SXPRIM = ''
    LEFT OUTER JOIN SVPFClean SV ON SVSEQ = SXSEQ
    LEFT OUTER JOIN BGPF ON BGCUS = GFCUS AND BGCLC = GFCLC
    LEFT OUTER JOIN NEPF NE ON SCAB=NE.NEAB and SCAN=ne.NEAN and SCAS=ne.NEAS
    LEFT OUTER JOIN SAPF SA ON SCAB=SAAB and SCAN=SAAN and SCAS=SAAS
WHERE 
    (SATCD>500 and
    scsac='IV' and
    scbal = 0 and
    scai30<>'Y' and
    scai14<>'Y' and
    not exists(select * from v5pf where v5and=scan and v5bal<>0)) and
    [Row] = 1
GROUP BY GFCUS, Ne.NEEAN, SCDLE, SAPOD, SATCD,
    CUS.GFCUN, BGCFN1, BGCFN2, BGCFN3, SV.SVCSA, 
     SV.SVNA1, SV.SVNA2, SV.SVNA3, SV.SVNA4, SV.SVNA5, SV.SVPZIP, SV.NONUK
ORDER BY MAX(SCAN) ASC

You could encapsulate this within a subquery if you don't want to return the [Row] column.

Upvotes: 1

radar
radar

Reputation: 13425

you can user row_number to get top 1 row per customer

In the where clause need to return values with pos value as 1

sample query

row_number() over ( partition by GFCUS order by SAPOD desc) as pos

Upvotes: 1

Related Questions