| View previous topic :: View next topic |
| Author |
Message |
dhulipalla
Beginner
Joined: 24 Aug 2004
Posts: 21
Topics: 6
|
 Posted: Mon May 21, 2007 10:25 am Post subject: Put a Seqnum for all rows selected |
 |
|
Hi,
Is there a way I can get the desired result.
| Code: |
COLA COLB
---- ----
AAA
AAA
AAA
BBB
BBB
BBB
EXPECTED RESULT:
---------------
COLA COLB
---- ----
AAA 1
AAA 2
AAA 3
BBB 1
BBB 2
BBB 3
|
Thanks,
Krishna |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon May 21, 2007 10:46 am Post subject: |
|
|
dhulipalla,
Not possible with the existing features of DB2
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany
|
| Posted: Tue May 22, 2007 1:38 am Post subject: |
|
|
kolusu,
dhulipalla,
if you have the special situation
- table with unique primary key
- selecting rows for a range of the primary key or all rows (see sample)
you you try this
| Code: |
SELECT mykey , ROW# FROM xyz.mytab TB1,
TABLE (SELECT COUNT(*) AS ROW#
FROM xyz.mytab TB2
WHERE TB2.mykey < TB1.mykey) AS TEMP_TAB
ORDER BY 2
|
But in general I agree, it's not possible.
regards,
bauer |
|
| Back to top |
|
|
bdahelj
Beginner
Joined: 24 Apr 2007
Posts: 1
Topics: 0
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue May 22, 2007 6:04 am Post subject: |
|
|
bauer,
The query you have shown will only give you a seqnum for all the rows, but if you look at Op's request he needs to restart the seqnum for every key change. So your sql will not solve Op's problem even though he has a unique primary key.
dhulipalla,
1. Unload the table
2. Run the unload file thru sort. sort has the capability of numbering the duplicate rows.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Aug 06, 2007 10:37 am Post subject: Online analytical processing (OLAP) specifications |
|
|
As an update to this thread, the OLAP functions provided by Vsn8 & 9 of DB2 UDB provide interesting resetting & running totals aggregation functions.
The OLAP function RANK DENSE would work in this case if there was a second column in the row (that need not be selected) but was distinct within each first column group.
e.g.
| Code: |
COLUMN FIRST SECOND
VALUE AAA 1
AAA 2
AAA 3
BBB 4
BBB 5
CCC 6
CCC 7
|
query
| Code: |
SELECT FIRST
, DENSE_RANK() OVER
(PARTITION BY FIRST ORDER BY SECOND ASC) AS RANK_EDLEVEL
FROM <TABLE>
ORDER BY FIRST, RANK_EDLEVEL;
|
results
| Code: |
AAAA 1
AAAA 2
AAAA 3
BBBB 1
BBBB 2
CCCC 1
CCCC 2
|
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Aug 06, 2007 10:46 am Post subject: |
|
|
dbzTHEdinosauer,
RANK function is NOT available on Z/OS DB2
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Aug 06, 2007 12:32 pm Post subject: |
|
|
dbzTHEdinosauer,
I stand corrected  Sorry about that.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Aug 06, 2007 2:24 pm Post subject: |
|
|
Once every 2 or 3000 answers; not a bad average.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
