sql - Auto Increment for non primary key column in Oracle -

i want insert records particular column increment 1 whenever new row gets inserted table based on following condition current year first row value :1 current year column value should increment 1 meaning 1 1st record of current year , next available number matching year

year  value 2016    1 2016    2 2016    3 2017    1 2017    2 ... 

my approach this:

insert abc(analysis_year,analysis_number) values (extract(year sysdate),         case when analysis_year=extract(year sysdate) autoicreamt starting value 1 else 1;     ) 

any solution looks @ current table values not work in 'real' environment multiple users , multiple sessions , parallel transactions.

i think need separate out 2 requirements:

1. have ability sequence records based on when created
2. have ability report on record sequencing within year.

the first handled using sequence these designed , handle concurrency (multiple users, multiple transactions, ...).

the second reporting requirement , has number of options depending on performance requirements.

first of create sequence:

create sequence seq_analysis_id start 1 increment 1 nocache nocycle; 

not let's create base table , trigger handle auto-increment:

create table analysis_data (     analysis_id integer not null,     analysis_date date not null     );  alter table analysis_data add constraint pk_analysis_data primary key (analysis_id);  create or replace trigger trg_analysis_data before insert on analysis_data each row begin     :new.analysis_id := seq_analysis_id.nextval(); end trg_analysis_data; /  insert analysis_data (analysis_date) values (to_date('2015-12-28', 'yyyy-mm-dd'));  insert analysis_data (analysis_date) values (to_date('2015-12-29', 'yyyy-mm-dd'));  insert analysis_data (analysis_date) values (to_date('2015-12-30', 'yyyy-mm-dd'));  insert analysis_data (analysis_date) values (to_date('2015-12-31', 'yyyy-mm-dd'));  insert analysis_data (analysis_date) values (to_date('2016-01-01', 'yyyy-mm-dd'));  insert analysis_data (analysis_date) values (to_date('2016-01-02', 'yyyy-mm-dd'));  insert analysis_data (analysis_date) values (to_date('2016-01-03', 'yyyy-mm-dd'));  commit;  select * analysis_data;  analysis_id    analysis_date     1           28/12/2015     2           29/12/2015     3           30/12/2015     4           31/12/2015     5           01/01/2016     6           02/01/2016     7           03/01/2016 

ok - works fine doesn't give asked :)

this second part - reporting requirement:

the first option numbers need dynamically:

select     analysis_id,     analysis_date,     extract(year analysis_date) analysis_year,     row_number()         on (partition trunc(analysis_date, 'yyyy')         order analysis_date, analysis_id) analysis_number     analysis_data; 

using analytic functions (row_number in case) great way handle sort of thing.

analysis_id analysis_date   analysis_year    analysis_number     1         28/12/2015      2015              1     2         29/12/2015      2015              2     3         30/12/2015      2015              3     4         31/12/2015      2015              4     5         01/01/2016      2016              1     6         02/01/2016      2016              2     7         03/01/2016      2016              3 

i've ordered analysis_date, analysis_id in row_number function. isn't necessary needed if had handle updates analysis_date (in case sequence no longer works in-year ordering on own).

you make little more straightforward reporting wrapping in view:

create or replace view analysis_data_v select     analysis_id,     analysis_date,     extract(year analysis_date) analysis_year,     row_number()         on (partition trunc(analysis_date, 'yyyy')         order analysis_date, analysis_id) analysis_number     analysis_data; 

this may need, if have large data sets may need pre-calculate of these values. have virtual columns in 11g, these don't work analytic functions. option here use materialized view - lots of ways handle materialized view refreshes , simplest be:

create materialized view analysis_data_mv      build immediate     refresh complete on demand      select         analysis_id,         analysis_date,         analysis_year,         analysis_number             analysis_data_v;  select * analysis_data_mv order analysis_year, analysis_number;  analysis_id analysis_date   analysis_year    analysis_number     1         28/12/2015      2015              1     2         29/12/2015      2015              2     3         30/12/2015      2015              3     4         31/12/2015      2015              4     5         01/01/2016      2016              1     6         02/01/2016      2016              2     7         03/01/2016      2016              3 

in case materialized view manually refreshed:

exec dbms_mview.refresh('analysis_data_mv'); 

hope helps.