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

create table hops.t_distribution AS
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!

  • 1
    You can order by in a create table statement. Post actual version and error message. Commented Dec 10, 2020 at 17:40

1 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.


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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.