Limiting query to 10 groups 1

[Kebabmeister]

Hi

Don't know if this is the forum for this but I'm writing an Oracle 8 SQL query as below and want to restrict the output to just the top 10 groups. Does anyone know the way?

SELECT LOCATIONS.DESCRIPTION, COUNT(*)
FROM WORKORDER, LOCATIONS
WHERE WORKORDER.LOCATION = LOCATIONS.LOCATION AND WORKORDER.SITEID = LOCATIONS.SITEID AND
WORKORDER.ORGID = LOCATIONS.ORGID AND WORKORDER.SITEID = :mroSite
GROUP BY LOCATIONS.DESCRIPTION
ORDER BY COUNT(*) DESC

 

[slaing]

You could create a view :

CREATE OR REPLACE VIEW view_xxx AS
SELECT WORKORDER.SITEID, LOCATIONS.DESCRIPTION, COUNT(*) loc_count
FROM WORKORDER, LOCATIONS
WHERE WORKORDER.LOCATION = LOCATIONS.LOCATION AND WORKORDER.SITEID = LOCATIONS.SITEID AND
WORKORDER.ORGID = LOCATIONS.ORGID
GROUP BY WORKORDER.SITEID, LOCATIONS.DESCRIPTION
ORDER BY loc_count DESC

and then
SELECT DESCRIPTION, LOC_COUNT
FROM view_xxx
WHERE SITEID = 'BEDFORD'
AND ROWNUM <= 11
/

I'm sure someone will come up with a more elegant solution, but it's a start !

HTH

Steve

 

[Kebabmeister]

Thanks.

Elegant or not, your solution is much appreciated.

 

[SantaMufasa]

Kebab, The "elegant" solution is just a variation on slaing's solution. The key is to create an "in-line" view:

select description, cnt
from (SELECT LOCATIONS.DESCRIPTION, COUNT(*) cnt
        FROM WORKORDER, LOCATIONS
       WHERE WORKORDER.LOCATION = LOCATIONS.LOCATION
         AND WORKORDER.SITEID = LOCATIONS.SITEID
         AND WORKORDER.ORGID = LOCATIONS.ORGID
         AND WORKORDER.SITEID = :mroSite
       GROUP BY LOCATIONS.DESCRIPTION
       ORDER BY COUNT(*) DESC)
where rownum <= 10;

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.