Re: A SQL quiz, tough one

This may help. It's not absolutely correct, it will work wrong in rare case when you have 10th and 11th lines with equal values of Volume.

                  INSERT INTO ReportTable
                   (
                    LogID,
                    GroupName,
                    NetUser,
                    Volume,
                   )
                   SELECT NameID,
                          GroupName,
                          Name,
                          SUM(BytesIn + BytesOut)
                     FROM TableA, TableB, TableC, TableD
                    WHERE ....
                         ...
                         ..
                    AND 10 < 
                        (select count(*)
                          from (select 1 from {same tables as above}
                          where (sum(BytesIn) + sum(BytesOut) > {same from above}
							        {select}
		         and ... 
                           group by ...))
                    GROUP BY NameID,
                             GroupName,
                             Name;

By the way, after you'll insert lines into table, they will not have any particular order (rule 1 of normalization), so you'll have to use order by when selecting data from it. Above query will work in SQL 7.1 - 7.3, but only in 7.3 PL/SQL. Regards, Alex Filonov.

In <5j92dr$q0i_at_nntp6.u.washington.edu>, lesliet_at_u.washington.edu (L. Tseng) writes:
>
>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
Received on Sat Apr 26 1997 - 00:00:00 CDT