home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #31 / NN_1992_31.iso / spool / comp / database / oracle / 2646 < prev    next >
Encoding:
Text File  |  1992-12-29  |  1.7 KB  |  44 lines
  1. Newsgroups: comp.databases.oracle
  2. Path: sparky!uunet!stanford.edu!eos!aio!casivils
  3. From: casivils@lescsse.jsc.nasa.gov (craig sivils)
  4. Subject: Re: Explain THIS, please!
  5. Message-ID: <casivils.725585665@node_508ba>
  6. Sender: news@aio.jsc.nasa.gov (USENET News System)
  7. Organization: Lockheed Engineering and Sciences
  8. References: <1gtd91INN7ln@ef2007.efhd.ford.com> <1992Dec22.163411.557@hhcs.gov.au> <1992Dec23.150326.560@hhcs.gov.au>
  9. Date: 28 Dec 92 23:34:25 GMT
  10. Lines: 32
  11.  
  12. Given the following table
  13.  
  14. SQL> desc test
  15.  Name                            Null?    Type
  16.  ------------------------------- -------- ----
  17.  PART                                     CHAR(10)
  18.  COUNT
  19.  
  20. The following select statement returns the top X groups
  21.  
  22. SELECT   PART
  23. FROM     TEST TOPTEN
  24. GROUP BY PART
  25. HAVING &1>(SELECT COUNT(DISTINCT PART) FROM TEST
  26.            WHERE  PART IN ( SELECT UNIQUE(PART)
  27.                             FROM   TEST
  28.                             GROUP BY PART
  29.                             HAVING ((SUM(COUNT)>(SELECT SUM(COUNT) FROM TEST
  30.                                                  WHERE PART = TOPTEN.PART
  31.                                                  GROUP BY PART ) )OR
  32.                                     (SUM(COUNT)=(SELECT SUM(COUNT) FROM TEST
  33.                                                  WHERE PART = TOPTEN.PART
  34.                                                  GROUP BY PART ) AND
  35.                                                  PART > TOPTEN.PART ) ) ) )
  36. ORDER BY SUM(COUNT),PART
  37.  
  38.  
  39.                                 Craig
  40.  
  41. I realize that this could be optimized quite a bit, most noteably, the inner
  42. most subquerys for the sum of the count is redundant, but hey, redundant is
  43. a step up from impossible.
  44.