Re: Use ROWNUM with GROUP BY - ARRGH!

Ordered subqueries are a feature of 8i

Do do this in 8.0.5 the only way i see is to create a temporary table, do the select into the table then select from the table with an order by.

I suppose you could force it to be ordered by joining it against an indexed table that had all the integers and forcing an descending index scan and a merge join. Probably easier to just use a temporary table.

"Tom Williamson" <tom.williamson_at_home.com> writes:

> When I execute your query I get :
>
> SQLWKS> SELECT * FROM (
> 2> SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY
> table_name order
> 3> by count(*) desc
> 4> )
> 5> WHERE ROWNUM < 5;
> SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY table_name order
> *
> ORA-00907: missing right parenthesis
>
>
> The "missing right parenthesis" occurs at the ORDER clause of the subquery.
> (Note: I also tried the same code as a view.) Does this really work on
> your system? What version of Oracle are you running? I'm running 8.0.6.

--
greg Received on Sat Nov 20 1999 - 13:00:13 CST