Re: Fetch n rows from each group by.

[Billy Rowe <billyrowe@xxxxxxx>]

   If I understand what your trying to do, Try something like this:

   select region, sum( sales ) as sales
     from file
   group by region
   order by sum( sales ) desc
   fetch first 5 rows only

   hth

 I have used the FETCH n ROWS ONLY clause to return the first n records
 in a select statement, but I now want to return the first n from each
 group, and I'm damned if I can find any reference material for this. Can
 it be done?

 In essence I want to return the top 5 agents from each region based on
 sales. The following pseudo-SQL would show all the agents in reverse
 sales order within region:

 Select region, sales from file order by region, sales desc

 If I want to select the first 5 in the entire return set I use:

 Select region, sales from file order by region, sales desc fetch first 5
 rows only

 How do I write the SQL to return only the first 5 in each region without
 having to use RPG?