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: 45
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: 45
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 Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm

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