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

From: d2domer <d2domer_at_dtek.chalmers.se>
Date: 6 Dec 2004 04:15:01 -0800
Message-ID: <1102335301.111884.239360@f14g2000cwb.googlegroups.com>

I suggested this solution to Gosta and in case it might help someone else I'm posting it here too. I works only for "TOP 1" type queries since the subquery will only return one value

SELECT DISTINCT
e.surname, e.firstname
FROM Employee e, EmployeeBenefitRecord ebr, EmployeePensionOption epo, PensionOption po, PensionScheme ps, BenefitProgramme bp, BenefitGroup bg

WHERE 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 = @companyId
AND ( ebr.id = (
SELECT DISTINCT first_value(ebr2.id) OVER (ORDER BY ebr2.startDate DESC)
FROM EmployeeBenefitRecord ebr2
WHERE ebr2.employeeId = e.id AND ebr2.status IN ('P','L', 'I') ))

Erik Received on Mon Dec 06 2004 - 06:15:01 CST