Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 40
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: 40
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am
No new posts comparing comp-3 and unpacked numbers juares castro COBOL Programming 3 Mon May 30, 2016 6:46 pm
No new posts Sort Trick for numbers pshongal DFSORT/ICETOOL 4 Tue Mar 22, 2016 1:33 pm
No new posts Column into multiple rows V S Amarendra Reddy SYNCSORT 2 Thu Mar 03, 2016 8:22 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us