Creating a table with Rank ordering in Oracle [closed]

Question

I am trying to create a table in Oracle as follow:

create table hops.t_distribution AS
(select 
UID, 
CONTACT_EMAIL, 
PRODUCT_ID, 
rank() over(Partition by UID order by to_number(PRODUCT_ID)) as PRIORITY_ORDER 
from hops.staging_test);

hops.staging_test is basically the same table without the PRIORITY_ORDER column.

However, I am getting an ORA error because you can't order by in a create table statement. Is there another clever way to do this?

Thank you!

Answer 1

The way you put it, it is not that ORDER BY causes problems, but the fact that UID is a reserved word and can't be used as a column name:

SQL> create table test (uid number);
create table test (uid number)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier

But, if you enclosed it into double quotes while creating the table, then yes - it'll "work" (but - do you really want to do that? I wouldn't):

SQL> create table test ("uid" number);

Table created.

SQL>

So: presuming that this is the case, then enclose it into double quotes while running your query. Pay attention to letter case! You MUST match it every time you use that column. Presuming it is in upper case, then you'd

SQL> create table t_distribution as
  2    select "UID", contact_email, product_id,
  3           rank() over (partition by "UID" order by product_id)
  4             priority_order
  5    from staging_test;

Table created.