ROW_NUMBER, as Dimitry suggests, is available from Firebird 3. The following query should work regardless of Firebird version:
SELECT s.* FROM SALARIES s LEFT JOIN SALARIES s2 on s.EMPID = s2.EMPID and s.EFFDATE < s2.EFFDATE LEFT JOIN SALARIES s3 on s2.EMPID = s3.EMPID and s2.EFFDATE < s3.EFFDATE WHERE s3.EMPID IS NULL If there are any rows without EMPID or EFFDATE, they are ignored by this query. Note that the query doesn't return the latest two rows, but all rows for the latest two EFFDATEs for the employee. If you have such duplicates and want the last two rows only, then you could e.g. do: SELECT s.* FROM SALARIES s LEFT JOIN SALARIES s2 on s.EMPID = s2.EMPID and ( s.EFFDATE < s2.EFFDATE or ( s.EFFDATE = s2.EFFDATE and s.ID < s2.ID ) ) LEFT JOIN SALARIES s3 on s2.EMPID = s3.EMPID and ( s2.EFFDATE < s3.EFFDATE or ( s2.EFFDATE = s3.EFFDATE and s2.ID < s3.ID ) ) WHERE s3.EMPID IS NULL HTH, Set ons. 7. aug. 2019 kl. 15:53 skrev Dimitry Sibiryakov [email protected] [firebird-support] <[email protected]>: > 07.08.2019 14:26, [email protected] [firebird-support] wrote: > > Is this possible in a single SELECT query > > I would try WHERE ROW_NUMBER() OVER (PARTITION BY empid, ORDER BY > effdate) <= 2. > > > -- > WBR, SD. > > > ------------------------------------ > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > ------------------------------------ > > Yahoo Groups Links > > > >
