Re: Explain THIS, please!

In article <1gtd91INN7ln_at_ef2007.efhd.ford.com>, wwm_at_ef5003.efhd.ford.com (Bill Meahan) writes:
> I'm tearing my hair out at the following:
>
> SQL> l
> 1 select '%' Shift,
> 2 p.prefix||'-'||p.base_code||'-'||p.suffix||p.control_code PartNumber,
> 3 s.scrap_code Reason,
> 4 SUM(s.cost) TotalDollars
> 5 FROM cimcmms.scrap_count_history s, acg.part p
> 6 WHERE s.part_key=p.key
> 7 AND s.work_center_code = '75021'
> 8 AND s.production_date BETWEEN '1-Dec-92' AND '13-Dec-92'
> 9 AND s.shift LIKE '%'
> 10 AND s.part_key IN ( SELECT ss.part_key
> 11 FROM cimcmms.scrap_count_history ss
> 12 WHERE ss.work_center_code = '75021'
> 13 AND ss.production_date BETWEEN '1-Dec-92' AND '13-Dec-92'
> 14 AND ss.shift LIKE '%'
> 15 AND ROWID <11

                


>  16       GROUP BY ss.part_key


>  17       ORDER BY SUM(ss.cost) desc )

            **************************
            **************************

It looks to me like you're trying to retreive the 10 parts with the highest sum(cost). As far as I can tell it's impossible to retrieve the top 10 groups of anything in a single query. You can retrieve the top ten of something, but not the top 10 GROUPS of something. Your only chance is to use PL/SQL cursors (though you'll have to put the results of the query in a temporary table so that you can see them after the PL/SQL has finished, as the results of PL/SQL statements don't echo to the screen).

Hope this helps.

    /\       Steven Champness
   /  \      Dept Health Housing and Community Services
  /    \     Brisbane Queensland
 /      \     via

  \/ / /                                it wasn't designed properly in the
   \/ /                                 first place !