hello,
In my 11.2.0.3 oracle database I have a table that holds a "physical order" which is maintained by the system.
for some of the entries I have to recalculate a "logical order" based on the "physicalorder" but ignoring some entries in the table. No problem with that since row_number function is a friend of mine.
with my_data as (
select 371 as PK, 2 as physical_index from dual union all
select 381 as PK, 5 as physical_index from dual union all
select 391 as PK, 9 as physical_index from dual union all
select 401 as PK, 7 as physical_index from dual
)
SELECT md.*
, ROW_NUMBER( ) OVER (ORDER BY physical_index) AS logical_index
FROM my_data md;
"pk","physical_index","logical_index"
371, 2, 1,
381, 5, 2,
391, 7, 3,
401, 9, 4,
This table is about to get another field "fixed position" where the user can enter a index at which this entry should rather be than what simple row_number() functon gives
with my_data as (
select 371 as PK, 2 as physical_index, 3 as fixed_index from dual union all
select 381 as PK, 5 as physical_index, null as fixed_index from dual union all
select 391 as PK, 7 as physical_index, null as fixed_index from dual union all
select 401 as PK, 9 as physical_index, 2 as fixed_index from dual
)
SELECT md.*
, null as logical_index -- what to write here?
FROM my_data md;
should give
"pk","physical_index","fixed_index","logical_index"
371, 2, 3, 3,
381, 5, , 1,
391, 7, , 4,
401, 9, 2, 2,
is this possible with a single SQL?
bye
TPD