I'm working on my Oracle SQL skills and trying to get a groups with TOP n records for each group. This works in Access/SQL Server using a correlated subquery that selects top n records ordering by the pertinent field and then selecting based on correlation to a join field in the outer query.
This should work, but I'm getting a "missing right parenthesis" error. What gives?
[tt]select s.site_id,p.pat_id,p.enroll_date
from sites s,patients p
where s.site_id=p.site_id
and p.pat_id in (select pt.pat_id
from patients pt
where pt.site_id=s.site_id and rownum < 4
order by pt.enroll_date);[/tt]
Thanks!
Jeff Roberts
Insight Data Consulting
Access and SQL Server Development
This should work, but I'm getting a "missing right parenthesis" error. What gives?
[tt]select s.site_id,p.pat_id,p.enroll_date
from sites s,patients p
where s.site_id=p.site_id
and p.pat_id in (select pt.pat_id
from patients pt
where pt.site_id=s.site_id and rownum < 4
order by pt.enroll_date);[/tt]
Thanks!
Jeff Roberts
Insight Data Consulting
Access and SQL Server Development