[Solved] SQL query error: “right parenthesis missing missing” [duplicate]

by


You can’t use AS tbltemp to alias a table in Oracle. The AS keyword can only used to alias columns, not tables. You can either remove the AS keyword, or in this case since you don’t refer to the alias, remove the whole AS tbltemp part. Here’s an SQL Fiddle.

It looks like the parser initially tries to interpret AS as the alias name, and then doesn’t know what the tbltemp is supposed to mean.

ZZa’s approach is better anyway, but you could also use anayltic functions to avoid hitting the tables multiple times:

select hospitalid, hname, htype from (
  select hospitalid, hname, htype, doc_count,
    rank() over (order by doc_count desc) as rn
  from (
    select h.hospitalid, h.hname, h.htype,
        count(d.doctorid) as doc_count
    from hospital h
    join doctor d on d.hospitalid = h.hospitalid
    group by h.hospitalid, h.hname, h.htype
  )
)
where rn = 1;

Another SQL Fiddle here. The innermost select does the counting, the next level ranks the grouped results in descending order of the number of doctors at each hospital, and the outermost restricts that to the highest-ranked.

Either way, if there’s a tie – two hospitals with the same number of doctors – you’ll get multiple rows back. If that’s not what you want you’d need to decide how to break the tie.

solved SQL query error: “right parenthesis missing missing” [duplicate]