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

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

Original text of this message