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
 
Row-Numbers of distinct rows?

 
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: 60
Location: Lower Saxony (DE)

PostPosted: Thu Oct 20, 2016 4:38 pm    Post subject: Row-Numbers of distinct rows?
Reply with quote

Hi there,

a new question:
Background: We have a table with some more than tree columns but with a pk column (let's call it PK_COL), a fk column (FK_COL) and third column with depending values (VAL_COL) which may contain duplicate values depending to one fk.
Sample:

Code:
PK_COL  FK_COL  VAL_COL
01      01      Alpha
02      01      Bravo
03      01      Alpha
04      01      Charlie
05      01      Bravo
06      01      Alpha


Thanks to all for your hints
The target is to SELECT DISTINCT FK_COL and VAL_COL but numbered by ROW_NUMBER.

I've gut a solution wit a sub-select
Code:
select   FK_COL
       , VAL_COL
       , row_number()
         over (partition by FK_COL
               order     by FK_COL
                          , VAL_COL) RNUM
  from (select distinct
                   FK_COL
                 , VAL_COL
          from     MY_DEP_TABLE
       )
  with  ur
;

But I am looking for a 'leaner' select. Is there a posibility to combine the DISTINCT clause with the row numbering?

When I was trying
Code:
   select     distinct
              FK_COL
            , VAL_COL
            , row_number()
              over (partition by FK_COL
                    order     by FK_COL
                               , VAL_COL) RNUM

the row numbers were interpreted as distinct values so the result containt duplicates :-(
Back to top
View user's profile Send private message

Auryn

New User


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

PostPosted: Mon Oct 24, 2016 5:40 pm    Post subject: Got it...
Reply with quote

More than 100 views (including my own) - Thanks for viewing and thinking about it. I appreciate that.

But: I've managed this question this morning: Just don't use the DISTINCT but the GROUP BY clause...
Code:
select    FK_COL
        , VAL_COL
        , row_number()
          over (partition by FK_COL) RNUM
  from    MY_DEP_TABLE
 group by FK_COL
        , VAL_COL
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 Delete Multiple rows in chunks by usi... dharmaraok DB2 5 Tue Sep 11, 2018 8:44 pm
No new posts Remove selected duplicate rows from t... hnharish DFSORT/ICETOOL 3 Wed Sep 05, 2018 3:11 am
No new posts Concatenaing multiple rows to single ... Vignesh Sid DFSORT/ICETOOL 1 Tue Aug 14, 2018 8:59 am
No new posts Random Numbers distributed evenly wit... grvtomar COBOL Programming 6 Sat Jul 21, 2018 3:34 am
No new posts Splitting one row into multiple Rows ... Rajan Moorthy DFSORT/ICETOOL 9 Mon May 14, 2018 10:58 pm

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