CODEWITHSUNDEEP
sqloracle-databaseplsql
dang
dang

Reputation: 2412

Add position column based on order by - Oracle

I have this table with the following records:

table1

id      ele_id_1    ele_val      ele_id_2       
1       2           123          1  
1       1           abc          1  
1       4           xyz          2  
1       4           456          1  
2       5           22           1
2       4           344          1  
2       3           6            1  
2       2           Test Name    1          
2       1           Hello        1

I am trying to add position for each id when ele_id_1 and ele_id_2 is order by ASC.

Here is the output:

id      ele_id_1    ele_val      ele_id_2       position
1       2           123          1              2
1       1           abc          1              1
1       4           xyz          2              4
1       4           456          1              3
2       5           22           1              5
2       4           344          1              4
2       3           6            1              3
2       2           Test Name    1              2
2       1           Hello        1              1

I have 34 million rows in table1, so would like to use an efficient way of doing this.

Any idea on how I can add position with values?

Upvotes: 0

Views: 682

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I think you want row_number() used like this:

select row_number() over (partition by id
                          order by ele_id_1, ele_id_2
                         ) as position

Oracle can use an index for this, on (id, ele_id_1, ele_id_2).

I should note that for your example data order by ele_id_1, ele_id_2 and order by ele_id_2, ele_id_1 produce the same result. Your question suggests that you want the first.

So, you would get

id      ele_id_1    ele_val      ele_id_2       position
1       1           123          2          2
1       1           abc          1          1
1       4           xyz          2          4
1       4           456          1          3

Rather than:

id      ele_id_1    ele_val      ele_id_2       position
1       1           123          2          3
1       1           abc          1          1
1       4           xyz          2          4
1       4           456          1          2

EDIT:

If you want to update the data, then merge is probably the best approach.

MERGE INTO <yourtable> dest
   USING (select t.*,
                 row_number() over (partition by id
                                    order by ele_id_1, ele_id_2
                                   ) as new_position
          from <yourtable> t
         ) src
         ON dest.id = src.id AND
            dest.ele_id_1 = src.ele_id_1 AND
            dest.ele_id_2 = src.ele_id_2          
   WHEN MATCHED THEN UPDATE
        SET desc.postition = src.new_position;

Note that updating all the rows in a table is an expensive operation. Truncating the table and recreating it might be easier:

create table temp_t as 
    select t.*, 
           row_number() over (partition by id
                              order by ele_id_1, ele_id_2
                             ) as new_position
    from t;

truncate table t;

insert into t ( . . . )
    select . . .  -- all columns but position
    from temp_t;

However, be very careful if you truncate the table. Be sure to back it up first!

Upvotes: 3

Related Questions