|
View previous topic :: View next topic
|
| Author |
Message |
ParagChouguley
Active User
.jpg)
Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)
|
|
|
|
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 |
|
 |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
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 |
|
|
ParagChouguley
Active User
Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)
|
|
|
|
Yups, ANY FIVE 
Had I been written first 5 and not said anything about order by, probably people would have killed me.
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| 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 |
|
|
ParagChouguley
Active User
Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)
|
|
|
|
Exactly ! You got it right !
--Parag |
|
| Back to top |
|
|
ParagChouguley
Active User
Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)
|
|
|
|
Hi ALL,
Please give a thought to it !
--Parag |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
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 |
|
|
ParagChouguley
Active User
Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)
|
|
|
|
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 |
|
|
|
|
