select sv.SVSEQ, sc.SCACO, ac.f1 as AccountNo, ne.NEAN, ne.NEEAN,bg.BGCFN1,bg.BGCFN2, bg.BGCFN3, bg.BGCUS,
sv.SVNA1 ,sv.SVNA2, sv.SVNA3, sv.SVNA4, sv.SVNA5, sv.SVPZIP,sv.SVCSA, sc.SCACO,sx.SXDLM
from NEPF ne
inner join tmpAccountList ac on NEEAN=ac.f1
inner join BGPF bg on bg.BGCUS=ne.NEAN
inner join SCPF sc on sc.SCAN=bg.BGCUS and bg.BGCUS=ne.NEAN and sc.SCAN=LEFT(ac.f1,6)
inner join sxpf sx on sx.SXCUS=LEFT(ac.f1,6)
INNER JOIN (
SELECT
SVSEQ, SVDLM, SVNA1, SVNA2, SVNA3, SVNA4, SVNA5, SVPZIP, SVCSA,
rn = ROW_NUMBER() OVER (PARTITION BY SVSEQ ORDER BY SVDLM)
FROM SVPF
) SV ON sv.SVDLM = sx.SXDLM AND rn = 1
Experiment by running the subquery on it's own to determine the best column to order by.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden