Help on ROW_NUMBER
-
I would need to make the column num1 have the value from column numa where the rent_ref column is null.
It has to be done by code as I need to run this over 5000000 records
The value from numa must be from the row after the nulls, so in this case rows 4, 5 and 6 need to have a num1 value of 6I use the following code to create the numa and num1 values
row_number() over (partition by p.prty_ref order by st_date) as numa
case when t.rent_ref is null then row_number() over (partition by p.prty_ref order by st_date)+1 else row_number() over (partition by p.prty_ref order by st_date) end as num1
Thanks in advance to any assistance given
- jezem - Thursday, May 31, 2018 7:10 AMI would need to make the column num1 have the value from column numa where the rent_ref column is null.
It has to be done by code as I need to run this over 5000000 records
The value from numa must be from the row after the nulls, so in this case rows 4, 5 and 6 need to have a num1 value of 6I use the following code to create the numa and num1 values
row_number() over (partition by p.prty_ref order by st_date) as numa
case when t.rent_ref is null then row_number() over (partition by p.prty_ref order by st_date)+1 else row_number() over (partition by p.prty_ref order by st_date) end as num1Thanks in advance to any assistance given
When you say "The value from numa must be from the row after the nulls" what defines the row order?
It also looks like your code is working as you have it - the num1 is numa + 1 where rent_ref is null. It's equal to numa where rent_ref is not null. - Ed Wagner - Thursday, May 31, 2018 7:21 AMjezem - Thursday, May 31, 2018 7:10 AMI would need to make the column num1 have the value from column numa where the rent_ref column is null.
It has to be done by code as I need to run this over 5000000 records
The value from numa must be from the row after the nulls, so in this case rows 4, 5 and 6 need to have a num1 value of 6I use the following code to create the numa and num1 values
row_number() over (partition by p.prty_ref order by st_date) as numa
case when t.rent_ref is null then row_number() over (partition by p.prty_ref order by st_date)+1 else row_number() over (partition by p.prty_ref order by st_date) end as num1Thanks in advance to any assistance given
When you say "The value from numa must be from the row after the nulls" what defines the row order?
It also looks like your code is working as you have it - the num1 is numa + 1 where rent_ref is null. It's equal to numa where rent_ref is not null.the code supplied only works for the very last null record, I need it to work for all 3 rows in this example.
The partition is by a filed called prty_ref with a order by a field called st_date - jezem - Thursday, May 31, 2018 7:10 AMI would need to make the column num1 have the value from column numa where the rent_ref column is null.
It has to be done by code as I need to run this over 5000000 records
The value from numa must be from the row after the nulls, so in this case rows 4, 5 and 6 need to have a num1 value of 6I use the following code to create the numa and num1 values
row_number() over (partition by p.prty_ref order by st_date) as numa
case when t.rent_ref is null then row_number() over (partition by p.prty_ref order by st_date)+1 else row_number() over (partition by p.prty_ref order by st_date) end as num1Thanks in advance to any assistance given
Without table create statements and inserts for at least this sample data, we have no way to see values for st_date or for prty_ref, so we have no idea what conditions those columns might introduce into the scenario. We also can't see fully what you are seeing. We'd also need to know what happens if a NULL value for rent_ref is the first row in a given set of rows for a given value of prty_ref. We need more details.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, May 31, 2018 7:58 AMjezem - Thursday, May 31, 2018 7:10 AMI would need to make the column num1 have the value from column numa where the rent_ref column is null.
It has to be done by code as I need to run this over 5000000 records
The value from numa must be from the row after the nulls, so in this case rows 4, 5 and 6 need to have a num1 value of 6I use the following code to create the numa and num1 values
row_number() over (partition by p.prty_ref order by st_date) as numa
case when t.rent_ref is null then row_number() over (partition by p.prty_ref order by st_date)+1 else row_number() over (partition by p.prty_ref order by st_date) end as num1Thanks in advance to any assistance given
Without table create statements and inserts for at least this sample data, we have no way to see values for st_date or for prty_ref, so we have no idea what conditions those columns might introduce into the scenario. We also can't see fully what you are seeing. We'd also need to know what happens if a NULL value for rent_ref is the first row in a given set of rows for a given value of prty_ref. We need more details.
This is the data with the 2 additional fields.
If the first row in a given set is null it takes the row number from the first row after the null.
If any rows in the middle in a given set are null it takes the row number from the first row after the null.
If the last row in a given set is null it need to take the row number from the preceding row - jezem - Thursday, May 31, 2018 8:19 AMsgmunson - Thursday, May 31, 2018 7:58 AMjezem - Thursday, May 31, 2018 7:10 AMI would need to make the column num1 have the value from column numa where the rent_ref column is null.
It has to be done by code as I need to run this over 5000000 records
The value from numa must be from the row after the nulls, so in this case rows 4, 5 and 6 need to have a num1 value of 6I use the following code to create the numa and num1 values
row_number() over (partition by p.prty_ref order by st_date) as numa
case when t.rent_ref is null then row_number() over (partition by p.prty_ref order by st_date)+1 else row_number() over (partition by p.prty_ref order by st_date) end as num1Thanks in advance to any assistance given
Without table create statements and inserts for at least this sample data, we have no way to see values for st_date or for prty_ref, so we have no idea what conditions those columns might introduce into the scenario. We also can't see fully what you are seeing. We'd also need to know what happens if a NULL value for rent_ref is the first row in a given set of rows for a given value of prty_ref. We need more details.
This is the data with the 2 additional fields.
If the first row in a given set is null it takes the row number from the first row after the null.
If any rows in the middle in a given set are null it takes the row number from the first row after the null.
If the last row in a given set is null it need to take the row number from the preceding rowOkay.. now how about the actual table create statements and insert statements that I asked for?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply