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

Row-Numbers of distinct rows?


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

PostPosted: Thu Oct 20, 2016 4:38 pm
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: 83
Location: Lower Saxony (DE)

PostPosted: Mon Oct 24, 2016 5:40 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Generate random number from range of ... COBOL Programming 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
Search our Forums:

Back to Top