Jamsplodge Jamsplodge - 5 months ago 8
SQL Question

How to do sorting and then numbering on an Oracle database

As an example I have a database with the following information

Name Number

Boris

Trevor

Arthur

bessie

big Dave

BOB


I want to be able to sort that data in the below order and then add a number to the number column in that specific order

Name Number

Arthur 1

BOB 2

Boris 3

big Dave 4

bessie 5

Trevor 6


I can select using the order I have specified using

select DB.TABLE.NAME , case
when row_number() over(partition by lower(DB.TABLE.NAME )
order by DB.TABLE.NAME ) = 1
then 1
else 0
end as result
from DB.TABLE;


but I then have no idea how to apply the numbers to the numbers column.
If I try a different method of sorting, I can use a sequence to apply the numbers but the order is not what I want. It seems to be the row_number() function that is causing me problems.

Any help would be appreciated.

Answer

Use a MERGE statement:

merge into the_table t
using (
    select rowid as rid,
           row_number() over(order by lower(name)) as result
   from the_table
) nr on (nr.rid = t.rowid)
when matched then update 
  set "number" = nr.result;

I am not sure what the CASE should do. It only returns 1 or 0 but the expected result shows you want numbers from 1 to 6, so I removed the CASE

If you have a proper primary key on the table, it's better to use that instead of rowid