Creating a table with Rank ordering in Oracle
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&gt; 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 &quot;work&quot; (but - do you really want to do that? I wouldn't):
SQL&gt; create table test (&quot;uid&quot; number);

Table created.

SQL&gt;

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&gt; create table t_distribution as
  2    select &quot;UID&quot;, contact_email, product_id,
  3           rank() over (partition by &quot;UID&quot; order by product_id)
  4             priority_order
  5    from staging_test;

Table created.