Get n records grouped by a certain field

[Sashi]

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

[Charles Hooper]

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.

[Sashi]

On Dec 11, 11:05 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On Dec 11, 10:25 am, Sashi <small...@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...

>
> 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.

Hi Charles, thanks for the reply. That worked well!
Sashi