Re: A SQL quiz, tough one

L. Tseng wrote:
>
> Hi, SQL experts,
>
> There is a non-trivial SQL question for you...
> In the following code, what I intend to do is to select top 10 users with most network traffic
> volume and insert it into a table in DESC order for reporting purpose.
> as you can see, I marked two lines off because 'ROWNUM <= 10' clause generates a wrong data set
> and 'ORDER by' clause is not legal in a INSERT.
> So, any smart solutions for this?
>
> Thanks for your help.
> Leslie
>
> INSERT INTO ReportTable
> (
> LogID,
> GroupName,
> NetUser,
> Volume,
> )
> SELECT NameID,
> GroupName,
> Name,
> SUM(BytesIn + BytesOut)
> FROM TableA, TableB, TableC, TableD
> WHERE ....
> ...
> ..
> -- AND ROWNUM <= 10
> GROUP BY NameID,
> GroupName,
> Name;
> -- ORDER BY 4 DESC
Any solution involving a single SQL statement will be *much* less efficient than a simple PL/SQL procedure which uses a cursor for the select statement (with its group by and order by clauses), fetching just 10 rows and inserting a row into your report table for each row retrieved.

Chrtsalis. Received on Sat Apr 19 1997 - 00:00:00 CDT