These SQL are able to add a column that has incrementing numbers (1, 2, 3, 4, etc.)
select year, quarter, uploaddate, wagecount, SUM(1)
OVER (ORDER BY year, quarter, uploaddate) as SequentialNumbers from SAMPLE_DATA
order by year, quarter, uploaddate
or
select your_query.*, rownum
from (select year, quarter, uploaddate, wagecount
from SAMPLE_DATA) your_query
Result look like this
| Year | Quarter | UploadDate | WageCount | SequentialNumbers |
|---|---|---|---|---|
| 2021 | 1 | 01-JAN-21 | 2 | 1 |
| 2021 | 1 | 02-JAN-21 | 2 | 2 |
| 2021 | 1 | 03-JAN-21 | 1 | 3 |
| 2021 | 1 | 04-JAN-21 | 5 | 4 |
Is there anyway to take the next bold step of making each year have its own sequential numbers. In other words results would ultimately look like this
something like this
| Year | Quarter | UploadDate | WageCount | SequentialNumbers |
|---|---|---|---|---|
| 2019 | 1 | 01-JAN-21 | 2 | 1 |
| 2019 | 1 | 02-JAN-21 | 2 | 2 |
| 2019 | 1 | 03-JAN-21 | 1 | 3 |
| 2019 | 1 | 04-JAN-21 | 5 | 4 |
| 2020 | 1 | 01-JAN-20 | 2 | 1 |
| 2020 | 1 | 02-JAN-20 | 2 | 2 |
| 2020 | 1 | 03-JAN-20 | 1 | 3 |
| 2020 | 1 | 04-JAN-20 | 5 | 4 |
| 2021 | 1 | 01-JAN-21 | 2 | 1 |
| 2021 | 1 | 02-JAN-21 | 2 | 2 |
| 2021 | 1 | 03-JAN-21 | 1 | 3 |
| 2021 | 1 | 04-JAN-21 | 5 | 4 |
Sample data is as follows:
CREATE TABLE SAMPLE_DATA (
YEAR VARCHAR2(4) NULL,
QUARTER NUMBER(1,0) NULL,
UPLOADDATE DATE NULL,
WAGECOUNT NUMBER(10,0) NULL
);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('27-MAR-19','DD-MON-RR'),5);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('28-MAR-19','DD-MON-RR'),8493);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('29-MAR-19','DD-MON-RR'),15070);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2019','1',to_date('30-MAR-19','DD-MON-RR'),1244);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('03-JAN-20','DD-MON-RR'),0);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('05-JAN-20','DD-MON-RR'),2);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('06-JAN-20','DD-MON-RR'),3);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2020','1',to_date('07-JAN-20','DD-MON-RR'),6);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('21-APR-21','DD-MON-RR'),59);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('22-APR-21','DD-MON-RR'),10);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('23-APR-21','DD-MON-RR'),16);
Insert into SAMPLE_DATA (YEAR,QUARTER,UPLOADDATE,WAGECOUNT) values ('2021','2',to_date('24-APR-21','DD-MON-RR'),1);
You need row_number analytic function:
select year, quarter, uploaddate, wagecount,
row_number()
OVER (partition by year
ORDER BY quarter, uploaddate) as SequentialNumbers
from SAMPLE_DATA
order by year, quarter, uploaddate;
Results:
YEAR QUARTER UPLOADDATE WAGECOUNT SEQUENTIALNUMBERS
---- ---------- ------------------- ---------- -----------------
2019 1 2019-03-27 00:00:00 5 1
2019 1 2019-03-28 00:00:00 8493 2
2019 1 2019-03-29 00:00:00 15070 3
2019 1 2019-03-30 00:00:00 1244 4
2020 1 2020-01-03 00:00:00 0 1
2020 1 2020-01-05 00:00:00 2 2
2020 1 2020-01-06 00:00:00 3 3
2020 1 2020-01-07 00:00:00 6 4
2021 2 2021-04-21 00:00:00 59 1
2021 2 2021-04-22 00:00:00 10 2
2021 2 2021-04-23 00:00:00 16 3
2021 2 2021-04-24 00:00:00 1 4