The T1 was missing following the first subselect.
Select t1.mlcode, t2.mldonr
(Select t1.mlcode From mmalib.mllchk2p t1 Group by t1.mlcode) T1
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
It was missing from my original example as well:
Select t1.Code, t2.member
(Select t1.Code From mylib.myfile t1 Group by T1.Code) T1
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: Friday, July 14, 2017 7:03 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Retrieve (n) records from each group
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
midrange.com
