IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

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: 67
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: 67
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

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts NOT ABLE TO FIND DB2 ROWS DB2 2
No new posts Need to read duplicate rows from tabl... DB2 3
No new posts to Insert Rows from File1 to File 2 ... DFSORT/ICETOOL 1
No new posts Multiple rows within Cursor when Coun... DB2 14
No new posts Feild level validation to test first ... JCL & VSAM 10

Back to Top