Re: Spooky SQL

As a practical matter, it would probably be faster just to write a PL/SQL procedure to loop through the sums descending and fetch the first five products and sums, like the others have suggested.

But as an academic matter, I was very intrigued by this, and wanted to find a way. Here is what I found. My table is small, so I do not know how slow this would be in practicality...

First the setup, then the answer, then the explanation: 1. Table BOBP

 Name                            Null?    Type
 ------------------------------- -------- ----
 PROD                                     VARCHAR2(10)
 QUANT                                    NUMBER

2. Inserted the following data:

 insert into bobp values ('A',3);
 insert into bobp values ('C',3);
 insert into bobp values ('C',2);
 insert into bobp values ('G',4);
 insert into bobp values ('G',8);
 insert into bobp values ('G',3);
 insert into bobp values ('G',5);
 insert into bobp values ('D',5);
 insert into bobp values ('D',5);
 insert into bobp values ('D',7);
 insert into bobp values ('B',3);
 insert into bobp values ('B',7);
 insert into bobp values ('F',8);
 insert into bobp values ('F',1);
 insert into bobp values ('E',7);
 insert into bobp values ('H',2);
 insert into bobp values ('H',2);
 insert into bobp values ('H',2);

Therefore, this adds up to:

PROD SUM(QUANT)
---------- ----------

G                  20
D                  17
B                  10
F                   9
E                   7
H                   6
C                   5
A                   3

Top five will be G,D,B,F,E

3. The full query:

select prod, sum(quant)
from bobp
group by prod
having sum(quant)>=
(select max(sum(quant))

   from bobp
 group by prod
 having sum(quant)<
  (select max(sum(quant))
   from bobp
   group by prod
   having sum(quant)<

     (select max(sum(quant))
      from bobp
      group by prod
      having sum(quant)<
        (select max(sum(quant))
         from bobp
         group by prod
         having sum(quant)<
           (select max(sum(quant))
            from bobp
            group by prod))))

4. Results of query.
PROD SUM(QUANT)
---------- ----------

G                  20
D                  17
B                  10
F                   9
E                   7

5. Explanation.
After the main query, read the sub-queries from the inside out: a. select prod, sum(quant)

    from bobp
    group by prod
    having sum(quant)>=
f. (select max(sum(quant))

    from bobp
    group by prod
    having sum(quant)<
e. (select max(sum(quant))

     from bobp
     group by prod
     having sum(quant)<
d.    (select max(sum(quant))
        from bobp
        group by prod
        having sum(quant)<
c.       (select max(sum(quant))
           from bobp
           group by prod
           having sum(quant)<
b.         (select max(sum(quant))
            from bobp
            group by prod))))

(a) produce the proper group by and summation of the desired
final output, including the order by DESC, and selects those groups greater than or equal to sub-criteria as follows:
(b) query selects the maximum sum, or the highest seller.
(c) query select the next maximum sum which is less than maximum seller
(or the second highest)
(d) query selects next maximum less than the others, or third highest.
(e) selects fourth highest maximum
(f) selects fifth highest maximum.

Caveats: Note that you will get more than five answers if any of the sums of quantity are identical...but this was academic anyway, right?

Good Luck

Robert Proffitt
Beckman Coulter
Brea California
RTProffitt_at_beckman.com

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 25 1999 - 18:46:58 CEST