| Subject | Re: [firebird-support] SELECT FIRST N BY GROUP |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2019-08-08T07:34:33Z |
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 sd@... [firebird-support] <firebird-support@yahoogroups.com>:
07.08.2019 14:26, pgore@... [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
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-digest@yahoogroups.com
firebird-support-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/