FROM keyword not found where expected (Oracle) ORA-00923
You need an alias for both derived tables. And the outer pair around the from clause is useless.

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM ( --&lt;&lt; only one opening parenthesis
  SELECT unique_id,
         confidence_is_same,
         first_name,
         last_name,
         postal_code
  FROM daniel.unique_physician
  WHERE daniel.unique_physician.first_name = ''
  AND   daniel.unique_physician.last_name = ''
  AND   daniel.unique_physician.is_root_phys = 0
  AND   daniel.unique_physician.postal_code = ''
) t1 ---&lt;&lt; the alias for the derived table is missing
  INNER JOIN (
    SELECT max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
    FROM daniel.unique_physician
    WHERE daniel.unique_physician.first_name = ''
    AND   daniel.unique_physician.last_name = ''
    AND   daniel.unique_physician.is_root_phys = 0
    AND   daniel.unique_physician.postal_code = ''
  ) t2 ON t1.confidence_is_same = t2.max_conf


But the join isn't needed in the first place. Your query can be simplified to:

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
  SELECT unique_id,
         confidence_is_same,
         max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
  FROM daniel.unique_physician unq
  WHERE unq.first_name = ''
  AND   unq.last_name = ''
  AND   unq.is_root_phys = 0
  AND   unq.postal_code = ''
) t1
where confidence_is_same = max_conf;


You don't need to select first_name, last_name and postal_code in the inner select as you don't use them in the outer select. This can make the query potentially more efficient.

Additionally, the condition unq.last_name = '' won't do what you think it does. Oracle does not have an "empty string". A string with length zero ('') will be stored as NULL, so what you really want is probably:

SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
  SELECT unique_id,
         confidence_is_same,
         max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
  FROM daniel.unique_physician unq
  WHERE unq.first_name is null
  AND   unq.last_name is null
  AND   unq.is_root_phys = 0
  AND   unq.postal_code is null
) t1
where confidence_is_same = max_conf;