Re: Get n records grouped by a certain field
Date: Fri, 11 Dec 2009 08:05:48 -0800 (PST)
On Dec 11, 10:25 am, Sashi <small..._at_gmail.com> wrote:
> Hi all,
> Let's say I have the canonical employee table. Let's say I have a
> dept_id field.
> Let's say that there are 1000+ employees in each department.
> Can it be done via sql that I need to see, say 10 employees from each
> select emp_last_name, dept_id
> from employee
> (but only fetch 10 employees for each department and I do want all
> departments selected).
> Hope that makes sense.
Yes, it is possible. Take a look at the following example: http://hoopercharles.wordpress.com/2009/11/30/sql-grouping-generating-comma-separated-lists/
If you read through that example, take a close look at what is shown
for the output of the first SELECT SQL statement. If you were to
slide that SQL statement into an inline view, you could then add a
WHERE clause like this to retrieve the first 10 employees sorted by
name from each of the departments:
RN <= 10
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 11 2009 - 10:05:48 CST