SQL Query to achieve control break in result set

prasun dhara · New User Joined: 01 Mar 2008 Posts: 49 Location: kolkata

Hi ,

I have a requirement like below :
Table A
Emp Id Role
1001 x
1001 y
1001 z
1002 c
1002 x
1003 a

I have to write a SQL query on this table to get a result like below

Counter Emp id Role
1 1001 x
2 1001 y
3 1001 z
1 1002 c
2 1002 x
1 1003 a

So I have to add a counter filed in the result set which will increament by one if the Emp id is same with previous one and the counter will re-set if the emp id changes.

Please note I have to do this by SQL query only and I am not allowed to write any COBOL logic

Thanks
Prasun

GuyC · Posted: Tue May 24, 2011 4:48 pm

homework!?
I suggest you look up OLAP-function in a SQL-book.
=> row_number() & partition by

prasun dhara · New User Joined: 01 Mar 2008 Posts: 49 Location: kolkata

It's Not home work. It's a project work

I found a query with
"row_number() & partition by" nut not working

Are these (row_number() & partition by) supported for Db2 zOS v8 ?

This is part of a performance tuning project. we have to implement this logic in SPUFI instead of COBOL

Thanks for your help

Pjoe · New User Joined: 22 Jul 2005 Posts: 17

row_number is not available in DB2 v8 for z /os.

dbzTHEdinosauer · Posted: Tue May 24, 2011 7:06 pm

why do you need these sequence numbers?

GuyC · Posted: Tue May 24, 2011 7:32 pm

trust me, implementing these sequence number in an sql pre v9 isn't performant :