× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have two examples, but having trouble with both --

Birgitta - "You want to have the first 5 rows for each Code and Member
Sorted by MLCode
Desc?"
-- YES, that is my desired outcome. So following your example, I get this
error...

With x as
(Select mlcode, mldonr,
Row_Number() Over(Partition By mlcode, mldonr Order By mlcode) Nbr
From x )
Select mlcode, mldonr
From mmalib.mllchk2p
Where Nbr between 1 and 5
Order By mlcode, mldonr
[SQL0346] Recursion not allowed for common table expressions. Cause . . .
. . : The common table expression specified is not valid. The subselect
for table X refers to itself. Recursive common table expressions are not
allowed. Recovery . . . : Change the common table expressions to refer to
a table that exists or a common table expression that has already been
defined. Try the request again.

*****
Chris’s example
Select t1.mlcode, t2.mldonr
(Select t1.mlcode From mmalib.mllchk2p t1
Group by t1.mlcode)
Cross Join Lateral( Select mlcode, mldonr from mmalib.mllchk2p t2
Where t2.mlcode = t1.mlcode
Order by mldonor
Fetch first 5 rows only
) t2 (mlcode, mldonr)
Order by t1.mlcode desc
[SQL0104] Token T1 was not valid. Valid tokens: ) ,. Cause . . . . . : A
syntax error was detected at token T1. Token T1 is not a valid token. A
partial list of valid tokens is ) ,. This list assumes that the statement
is correct up to the token.. . etc…..
******
So I added a comma after t2.mldonr on first line….and then error on join
Select t1.mlcode, t2.mldonr,
(Select t1.mlcode From mmalib.mllchk2p t1 Group by t1.mlcode)
Cross Join Lateral( Select mlcode, mldonr from mmalib.mllchk2p t2
Where t2.mlcode = t1.mlcode
Order by mldonor
Fetch first 5 rows only
) t2 (mlcode, mldonr)
Order by t1.mlcode desc

*********

I feel like I'm almost there, and I've been playing with sql to try to
accomplish this without writing a program to do get my results, but I
remember being at a COMMON, when one of the speakers stressed..... there
isn't anything you can't do in SQL... hmmm...so I keep on trying with
this. :) --
Thanks for the suggestions, I just need someone to grade my work ... :)

Diane






From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 07/14/2017 03:22 AM
Subject: 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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.