Re: Get n records grouped by a certain field

From: Sashi <smalladi_at_gmail.com>
Date: Fri, 11 Dec 2009 11:15:03 -0800 (PST)
Message-ID: <bc109ac1-a51b-4779-878c-0b98c6de02a7_at_p35g2000yqh.googlegroups.com>

On Dec 11, 11:05 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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...
>
> 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 Received on Fri Dec 11 2009 - 13:15:03 CST