ORDER BY in GROUP BY clause

I have a query

 Select
 (SELECT id FROM xyz M  WHEREM开发者_高级运维.ID=G.ID AND ROWNUM=1 ) TOTAL_X,
count(*) from mno G where col1='M' group by col2  

Now from subquery i have to fetch ramdom id for this I am doing

     Select
 (SELECT id FROM xyz M  WHEREM.ID=G.ID AND ROWNUM=1 order by dbms_random.value ) TOTAL_X,
count(*) from mno G where col1='M' group by col2 

But , oracle is showing an error

"Missing right parenthesis".

what is wrong with the query and how can i wrtie this query to get random Id. Please help.

---

Even if what you did was legal, it would not give you the result you want. The ROWNUM filter would be applied before the ORDER BY, so you would just be sorting one row.

You need something like this. I am not sure if this exact code will work given the correlated subquery, but the basic point is that you need to have a subquery that contains the ORDER BY without the ROWNUM filter, then apply the ROWNUM filter one level up.

WITH subq AS (
  SELECT id FROM xyz M  WHERE M.ID=G.ID order by dbms_random.value
)
SELECT (SELECT id FROM subq WHERE rownum = 1) total_x,
       count(*)
from mno g where col1='M' group by col2

---

You can't use order by in a subselect. It wouldn't matter too, because the row numbering is applied first, so you cannot influence it by using order by,

[edit]

Tried a solution. Don't got Oracle here, so you'll have to read between the typos. In this case, I generate a single random value, get the count of records in xyz per mno.id, and generate a sequence for those records per mno.id.

Then, a level higher, I filter only those records whose index match with the random value. This should give you a random id from xyz that matches the id in mno.

select
  x.mnoId,
  x.TOTAL_X
from
    (SELECT
      g.id as mnoId,
      m.id as TOTAL_X,
      count(*) over (partition by g.id) as MCOUNT,
      dense_rank() over (partition by g.id) as MINDEX,
      r.RandomValue
    from 
      mno g 
      inner join xyz m on m.id = g.id
      cross join (select dbms_random.value as RandomValue from dual) r
    where 
      g.col1 = 'M' 
    ) x
where
  x.MINDEX = 1 + trunc(x.MCOUNT * x.RandomValue)

---

The only difference between your two lines are that you order_by in the one that fails, right? It so happens that order_by doesn't fit inside a nested select.

You could do an order_by inside a where clause that contains a select, though.

Edit: @StevenV is right.

---

If you're trying to do what I suspect, this should work

Select A.Id, Count(*)
From MNO A 
Join (Select ID From XYZ M  Where M.ID=G.ID And Rownum=1 Order By Dbms_Random.Value ) B On (B.ID = A.ID)
GROUP BY A.ID