Re: How do I use ROWNUM in a subquery (emulating SQL Server TOP n)

From: Adrian Billington <billiauk_at_yahoo.co.uk>
Date: 1 Dec 2004 00:07:09 -0800
Message-ID: <dee17a9f.0412010007.355ce9f4@posting.google.com>

Gosta,

Seeing as Sybrand was his usual helpful self, I thought I might assist. Try the following. It uses analytics in an in-line view to window sort the EBR table into employees and their most recent record ( to get the ID etc ). It saves the self-join to the EBR table. Outside the in-line view, we just pick off a single row per employeeID.

Obviously I haven't created a bunch of tables and invented data as I just don't have time, so the following is untested. Let me know if there's any "tweaks" needed and I'll see if I can assist.

SELECT DISTINCT

       e.surname


,      e.firstname

FROM   Employee e
,     (
       SELECT employeeID
       ,      FIRST_VALUE(id) OVER
                 ( PARTITION BY employeeID ORDER BY startDate DESC )
AS id
       ,      FIRST_VALUE(benefitGroupId) OVER
                 ( PARTITION BY employeeID ORDER BY startDate DESC )
AS benefitGroupId
       ,      ROW_NUMBER() OVER
                 ( PARTITION BY employeeID ORDER BY startDate DESC )
AS rn
       FROM   EmployeeBenefitRecord
       WHERE  status IN ('P', 'L', 'I')
      ) ebr


,      EmployeePensionOption epo

,      PensionOption po

,      PensionScheme ps

,      BenefitProgramme bp

,      BenefitGroup bg

WHERE ebr.rn = 1 --take the top row per employee AND e.id = ebr.employeeId
AND ebr.id = epo.employeeBenefitRecordId AND ebr.benefitGroupId = bg.id
AND bp.id = bg.programmeId
AND epo.pensionOptionId = po.id
AND po.pensionSchemeId = ps.id
AND e.companyId = XPENSIONBANDCROSSING.companyId_ -- your e.g. was truncated
/

Regards
Adrian Received on Wed Dec 01 2004 - 02:07:09 CST