IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Group changes and numbering the groups


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Tue Mar 20, 2018 7:12 pm
Reply with quote

Hi everybody,

a question concerning group changes and numbering the groups:
We have a table with some more than three coloums but COL2 and COL3:
Code:
COL1 COL2    COL3
01   One     Alpha
02   Two     Bravo
03   Two     Charlie
04   Three   Delta
05   Three   Echo
06   Three   Foxtrot
07   Four    Golf
08   Four    Hotel
09   Four    India
10   Four    Juliet
11   Five    Kilo
12   Five    Lima
13   Five    Mike
14   Five    November
15   Five    Oscar
16   Six     Papa
17   Six     Quebec
18   Six     Romeo
19   Six     Sierra
20   Six     Tango
21   Six     Uniform
22   Seven   Victor
23   Seven   Whisky
24   Seven   X-Ray
25   Seven   Yankee
26   Seven   Zulu


I'd like to select the data not only with a row number partitioned by the group but also by a group number so that the result looks like
Code:
COL1 COL2    COL3       GRU_NUM   ROW_NUM
11   Five    Kilo             1         1
12   Five    Lima             1         2
13   Five    Mike             1         3
14   Five    November         1         4
15   Five    Oscar            1         5
07   Four    Golf             2         2
08   Four    Hotel            2         3
09   Four    India            2         4
10   Four    Juliet           2         6
01   One     Alpha            3         1
22   Seven   Victor           4         1
23   Seven   Whisky           4         2
24   Seven   X-Ray            4         3
25   Seven   Yankee           4         4
26   Seven   Zulu             4         5
16   Six     Papa             5         1
17   Six     Quebec           5         2
18   Six     Romeo            5         3
19   Six     Sierra           5         4
20   Six     Tango            5         5
21   Six     Uniform          5         6
04   Three   Delta            6         1
05   Three   Echo             6         2
06   Three   Foxtrot          6         3
02   Two     Bravo            7         1
03   Two     Charlie          7         2


It's easy to calculate the coloumn ROW_NUM by using the ROW_NUMBER() OVER (PARTITION BY COL2) clause.

But does anybody know an easy way to calculate the group numbers?
I was successful using an inner join and a double sub-select:
Code:
SELECT    a.col2
        , a.col3
        , b.gru_num
        , ROW_NUMBER() OVER (PARTITION BY   a.col2
                             ORDER     BY   a.col2
                                          , a.col3)
                                       AS   row_num
  FROM     my_table a
        , (SELECT    col2
                   , ROW_NUMBER ()
                           OVER (ORDER BY     col2)
                                       AS     gru_num
             FROM (SELECT DISTINCT col2
                     FROM my_table     )             )  b
 WHERE    a.col2                        =   b.col2
  WITH UR
;

But - is this really the most 'elegant' way? Does it really has to be that tricky?

Thank you very much for your hints
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Tue Mar 20, 2018 11:47 pm
Reply with quote

Quote:
But - is this really the most 'elegant' way? Does it really has to be that tricky?
No
Quote:
But does anybody know an easy way to calculate the group numbers?
Code:
DENSE_RANK() OVER (  ORDER BY col2) as GRU_NUM
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Wed Mar 21, 2018 1:46 pm
Reply with quote

Hey, great. It works.
Thank you very much.

Tried RANK() but was not happy with that.
Sometimes it's pretty helpful to continue RTFM...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Hexadecimal Group compare and write t... DFSORT/ICETOOL 5
No new posts DB2 group member usage DB2 0
No new posts Group comparison/update between two f... DFSORT/ICETOOL 10
No new posts Group selective VB records - Trailer ... DFSORT/ICETOOL 16
No new posts Include Group record based on detail ... DFSORT/ICETOOL 11
Search our Forums:

Back to Top