Re: Get n records grouped by a certain field
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 11 Dec 2009 08:05:48 -0800 (PST)
Message-ID: <e5d2c33b-b207-4ed9-b4e5-b2046f6ac7a7_at_k19g2000yqc.googlegroups.com>
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
> department?
>
> 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.
>
> TIA,
> Sashi
Date: Fri, 11 Dec 2009 08:05:48 -0800 (PST)
Message-ID: <e5d2c33b-b207-4ed9-b4e5-b2046f6ac7a7_at_k19g2000yqc.googlegroups.com>
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
> department?
>
> 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.
>
> TIA,
> Sashi
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:
WHERE
RN <= 10
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Dec 11 2009 - 10:05:48 CST