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
 
Group changes and numbering the groups

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Auryn

New User


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

PostPosted: Tue Mar 20, 2018 7:12 pm    Post subject: Group changes and numbering the groups
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

Senior Member


Joined: 21 Sep 2010
Posts: 1886
Location: NY,USA

PostPosted: Tue Mar 20, 2018 11:47 pm    Post subject:
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: 49
Location: Lower Saxony (DE)

PostPosted: Wed Mar 21, 2018 1:46 pm    Post subject:
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    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 How to group and sum the decimal value sakrat DFSORT/ICETOOL 2 Tue Mar 06, 2018 9:13 pm
No new posts Copy Specific Groups V S Amarendra Reddy SYNCSORT 10 Thu Jan 11, 2018 1:16 am
No new posts RACF - Sub groups - how they work? vasanthz JCL & VSAM 1 Wed Jan 10, 2018 6:44 am
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts Increment number for every group of r... srajendran2 DFSORT/ICETOOL 7 Thu Nov 09, 2017 10:31 pm

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