Re: Explain THIS, please!

From: craig sivils <casivils_at_lescsse.jsc.nasa.gov>
Date: 28 Dec 92 23:34:25 GMT
Message-ID: <casivils.725585665_at_node_508ba>

Given the following table

SQL> desc test

 Name                            Null?    Type
 ------------------------------- -------- ----
 PART                                     CHAR(10)

 COUNT The following select statement returns the top X groups

SELECT PART
FROM TEST TOPTEN
GROUP BY PART
HAVING &1>(SELECT COUNT(DISTINCT PART) FROM TEST

           WHERE  PART IN ( SELECT UNIQUE(PART)
                            FROM   TEST
                            GROUP BY PART
                            HAVING ((SUM(COUNT)>(SELECT SUM(COUNT) FROM TEST
                                                 WHERE PART = TOPTEN.PART
                                                 GROUP BY PART ) )OR
                                    (SUM(COUNT)=(SELECT SUM(COUNT) FROM TEST
                                                 WHERE PART = TOPTEN.PART
                                                 GROUP BY PART ) AND
                                                 PART > TOPTEN.PART ) ) ) )

ORDER BY SUM(COUNT),PART

                                Craig

I realize that this could be optimized quite a bit, most noteably, the inner most subquerys for the sum of the count is redundant, but hey, redundant is a step up from impossible. Received on Tue Dec 29 1992 - 00:34:25 CET