Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
This topic is locked: you cannot edit posts or make replies. Limit access to certain RACF group cvnlynn CLIST & REXX 5 Wed Aug 23, 2017 2:28 am
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us