ORA-00907: missing right parenthesis

Question

I'm getting the error " ORA-00907: missing right parenthesis" but I've checked and all the parenthesis are there, so I'm stumped.

My query is

SELECT 
  SUM(score) as score, 
  facebook_id, 
  firstname, 
  lastname, 
  dense_rank(score) 
WITHIN GROUP ( ORDER BY score ) as rank_db  
FROM 
  (
    SELECT DISTINCT *
    FROM 
      (
        SELECT *  
        FROM fanta_score 
        ORDER BY score desc
      ) as f 
    GROUP BY 
      facebook_id, game_id
  ) as g 
GROUP BY facebook_id
ORDER BY score DESC, created_at
LIMIT 50

I'm by no means an Oracle expert, but I have to use it due the hosting environment its has to be in.

I'm thinking it might be something to do with the limit statement??? — ZaV
Have you tried commenting out each piece of the query (working inside/out) and seeing which statement in particular generates the error? — Jordan Parmer
Also, this doesn't really solve your problem, but you don't want to have an ORDER clause in a subquery. That will kill your query performance because the optimizer can no longer use indexes once you sort because it has to flatten the result set. People will often ORDER in a subquery when they really don't need to ORDER. — Jordan Parmer
yea, i'm working through that now, but I don't have a local oracle test environment so it is a tedious process. — ZaV
distinct * and GROUP BY facebook_id, game_id in the same select doesn't make sense. Additionally the outer statement won't work, as you are not grouping by all non-aggregate columns. — a_horse_with_no_name

PPShein PPShein · Accepted Answer · 2011-11-03T02:24:05

LIMIT command isn't recognized in Oracle. And should use ROWNUM instead of Limit.

SELECT 
  SUM(score) as score, 
  facebook_id, 
  firstname, 
  lastname, 
  dense_rank(score) 
WITHIN GROUP ( ORDER BY score ) as rank_db  
FROM 
  (
    SELECT DISTINCT *
    FROM 
      (
        SELECT *  
        FROM fanta_score 
        ORDER BY score desc
      ) as f 
    GROUP BY 
      facebook_id, game_id
  ) as g
WHERE ROWNUM = 50 
GROUP BY facebook_id
ORDER BY score DESC, created_at

ORA-00907: missing right parenthesis

1 Answers