RE: Retrieve (n) records from each group

You want to have the first 5 rows for each Code and Member Sorted by MLCode
Desc?

If so, you may try the following statement.



With x as (Select Code, Member, ...

Row_Number() Over(Partition By Code, Member Order By
MLCode Desc) Nbr

From x)

Select Code, Member, ....

From YourTable

Where Nbr between 1 and 5

Order By Code, Member



Mit freundlichen Grüßen / Best regards



Birgitta Hauser



"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)

"If you think education is expensive, try ignorance." (Derek Bok)

"What is worse than training your staff and losing them? Not training them
and keeping them!"

?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)





-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
dmmueller@xxxxxxx
Sent: Donnerstag, 13. Juli 2017 20:45
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: <mailto:MIDRANGE-L@xxxxxxxxxxxx>
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,

visit: <http://lists.midrange.com/mailman/listinfo/midrange-l>
http://lists.midrange.com/mailman/listinfo/midrange-l

or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to
review the archives at <http://archive.midrange.com/midrange-l>
http://archive.midrange.com/midrange-l.



Please contact <mailto:support@xxxxxxxxxxxx> support@xxxxxxxxxxxx for any
subscription related questions.



Help support midrange.com by shopping at amazon.com with our affiliate link:
<http://amzn.to/2dEadiD> http://amzn.to/2dEadiD