The statement you have included has "mlcode" but you don't have that in your group by. Was that just a miskey of "code"?
A lateral join will work.
Generate a cte with the desired grouping, then you can use a lateral to go back and get the few of the full detail records.
Select t1.Code, t2.member
(Select t1.Code From mylib.myfile t1 Group by T1.Code)
Cross Join Lateral(
Select Code, Member from mylib.myfile t2
Where t2.code = t1.code
Order by Member
Fetch first 5 rows only
) t2 (CODE, MEMBER)
Order by t1.Code desc
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of dmmueller@xxxxxxx
Sent: Thursday, July 13, 2017 12:45 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: Retrieve (n) records from each group
All,
I want to pull a given amount of records from each "group by" category.
Given statement below, I only get (5) records from the first group. How can I change this to give me 5 records from each group?
select code, member from mylib.myfile
group by code, member
order by mlcode desc
Limit 5
Diane
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
