I am trying to add a column that assigns row numbers to each record

I made changes to the code to add the desired column

But I was getting duplicated row numbers. How do I get unique row numbers? .

**Note: This code can be executed in the SQL editor like that. It needs no sample data**

select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date, trunc(GenerateTimeBy1DayPlus20,'day') as bwe_to_completeby from
(
    select from_dt   (level - 1)/1 as GenerateTimeBy1Day, (from_dt   (level - 1)/1)   20 as GenerateTimeBy1DayPlus20 
    from (select from_dt
        ,to_dt
        ,to_dt - from_dt   1 as days_between    
    from (select to_date('03-Jan-2021') as from_dt
               , to_date('30-Jan-2021') as to_dt 
          from dual))
    connect by (level - 1) <= days_between  
)
order by claim_eff_date

I made these changes to the code to add the desired column

**Note: This code can be executed in the SQL editor like that. It needs no sample data**

select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date, trunc(GenerateTimeBy1DayPlus20,'day') as bwe_to_completeby, row_number() 
over (PARTITION BY trunc(GenerateTimeBy1Day,'day'), trunc(GenerateTimeBy1DayPlus20,'day') ORDER BY trunc(GenerateTimeBy1Day,'day')) as row_number from
(
    select from_dt   (level - 1)/1 as GenerateTimeBy1Day, (from_dt   (level - 1)/1)   20 as GenerateTimeBy1DayPlus20 
    from (select from_dt
        ,to_dt
        ,to_dt - from_dt   1 as days_between    
    from (select to_date('03-Jan-2021') as from_dt
               , to_date('30-Jan-2021') as to_dt 
          from dual))
    connect by (level - 1) <= days_between  
)
order by claim_eff_date

But I am getting

Row_Number
----------
1
1
2
3
4
5
6
1
1
2
3

How do I get unique row numbers?

Row_Number
----------
1
2
3
4
5
6
7
8
9
10

CodePudding user response：

I'm not sure whet those dates represent, but - if query you initially wrote does the job, then use it as source for the final query which calculates row number:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with your_query as (
  2  select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date,
  3                  trunc(GenerateTimeBy1DayPlus20,'day') as bwe_to_completeby
  4  from
  5  (
  6      select from_dt   (level - 1)/1 as GenerateTimeBy1Day, (from_dt   (level - 1)/1)   20 as GenerateTimeBy1DayPlus20
  7      from (select from_dt
  8          ,to_dt
  9          ,to_dt - from_dt   1 as days_between
 10      from (select date '2021-01-03' as from_dt
 11                 , date '2021-01-30' as to_dt
 12            from dual))
 13      connect by (level - 1) <= days_between
 14  ))
 15  select claim_eff_date,
 16         bwe_to_completeby,
 17         row_number() over (order by claim_eff_date) rn
 18  from your_query
 19  order by claim_eff_date;

CLAIM_EFF_ BWE_TO_COM         RN
---------- ---------- ----------
28.12.2020 18.01.2021          1
04.01.2021 25.01.2021          2
04.01.2021 18.01.2021          3
11.01.2021 01.02.2021          4
11.01.2021 25.01.2021          5
18.01.2021 08.02.2021          6
18.01.2021 01.02.2021          7
25.01.2021 08.02.2021          8
25.01.2021 15.02.2021          9

9 rows selected.

SQL>

By the way, this is what you wrote: to_date('03-Jan-2021'). That's wrong. TO_DATE applied to a string without date format mask relies on Oracle's capabilities to guess what you meant to say. Besides, that won't even work in my database, although you said

This code can be executed in the SQL editor

My database speaks Croatian and there's no month like Jan.

Safer option is to

• apply format mask: to_date('03-Jan-2021', 'dd-mon-yyyy', 'nls_date_language = english')
• or, use date literal (like I did): date '2021-01-03' which ALWAYS has date keyword and date in yyyy-mm-dd format, so there's no ambiguity