Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fetch First 5 rows from each group

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Tue Jan 15, 2008 4:16 pm    Post subject: Fetch First 5 rows from each group
Reply with quote

Hi ALL,
I have a DB2 table having one of its column as Employee No which has unique constraint. Few Sample values to this column are
Code:

Employee No
AX001
AX002
.
.
AX099
AM001
.
.
AM050
.
.



Now I want to retrive any 5 rows for each of employee group starting with AX,AM,AZ,BM,...... So any 5 employees for each group.

I want it to be done thru SQL Spufi.
--Parag
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jan 15, 2008 6:40 pm    Post subject:
Reply with quote

Ah, a correct question finally saying "ANY 5", not the first 5 that is so wrong. Even though the statement below says FIRST 5, we are using an order by to ensure consistent results. If the order by isn't there, then the resulting 5 would really be "ANY 5" that the DB chooses to give you.

Code:

SELECT * FROM this.table
WHERE LEFT(EmployeeNO,2) = 'AX'
ORDER BY EmployeeNO
FETCH FIRST 5 ROWS ONLY;
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Tue Jan 15, 2008 8:07 pm    Post subject:
Reply with quote

Yups, ANY FIVE icon_biggrin.gif
Had I been written first 5 and not said anything about order by, probably people would have killed me.
icon_biggrin.gif
But Steve, what about other employee numbers. Your SQL query returns rows only for one type of employee, ie starting with AX. (if I'm not wrong)

Quote:

Now I want to retrive any 5 rows for each of employee group starting with AX,AM,AZ,BM,...... So any 5 employees for each group.

I want it to be done thru SQL Spufi.


--Parag
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jan 15, 2008 8:10 pm    Post subject:
Reply with quote

So you want any five for each of the possible 2 character combinations that may be in the first 2 positions of an employee number?
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Wed Jan 16, 2008 7:32 pm    Post subject:
Reply with quote

Exactly ! You got it right !

--Parag
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Thu Jan 17, 2008 12:55 pm    Post subject:
Reply with quote

Hi ALL,
Please give a thought to it !

--Parag
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 17, 2008 9:02 pm    Post subject:
Reply with quote

Hello,

If you need this quickly, i'd suggest you go ahead and write a bit of actual code to do what you need. . .
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Jan 17, 2008 9:14 pm    Post subject:
Reply with quote

It looks like you will need to do this

Declare a cursor as follows:
Code:

 SELECT DISTINCT(LEFT(EmployeeNO,2))
 FROM this.table


Fetch data from the cursor row by row into WS-LEFT-TWO and then exec the following:
Code:

SELECT * FROM this.table
WHERE LEFT(EmployeeNO,2) = :WS-LEFT-TWO
ORDER BY EmployeeNO
FETCH FIRST 5 ROWS ONLY;
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Sun Jan 20, 2008 6:51 pm    Post subject:
Reply with quote

Dick and Steve,
Thanks for your replies !
Now its clear that it could be achieved thru writing a code !
But can it not be done thru SQL Query as per my initial requirement ?

--Parag
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 2 Wed Mar 22, 2017 7:46 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Sorting group data rajella DFSORT/ICETOOL 4 Sun Jan 22, 2017 11:32 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us