Chris,
yes.... "mlcode" was a miskey.
Have not played much with Cross Join Laterals... I'll test this following
your example and see how it works.
thanks for that example.
Diane
From: "Hiebert, Chris" <chris.hiebert@xxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/13/2017 03:18 PM
Subject: RE: Retrieve (n) records from each group
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
