Select the first n-groups from a query-CodePudding

I have this query...

SELECT * FROM
    (SELECT LIMCU, LIITM, SUM(LIPQOH) LIPQOH
     FROM PRODDTA.F41021
     WHERE [Many Conditions]
     GROUP BY LIMCU, LIITM)
WHERE ROWNUM <= [Number];

This query will group by two parameters (MCU and ITM), & then it will retrieve only a specific number of groups (ROWNUM).

The purpose of this query is to randomize testing (it's integrated into a PL/SQL script), so I will fill-in an inbound file & then test an application/report. I'm not interested in the data per se, I just want to grab what comes up first, process it & see how it goes.

The question is: is there a way in which I prevent the subquery from doing the entire grouping calculation & instead "integrate" the ROWNUM into it? I tried the following, naturally to no avail.

SELECT LIMCU, LIITM, SUM(LIPQOH) LIPQOH
     FROM PRODDTA.F41021
     WHERE [Many Conditions]
     GROUP BY LIMCU, LIITM 
     HAVING ROWNUM <= [Number];

CodePudding user response：

Using ORDER BY DBMS_RANDOM.VALUE will sort the rows in a random order each time the query is run. You can then use FETCH FIRST... to return just some of the results that were randomized.

See example below:

  SELECT LIMCU, LIITM, SUM(LIPQOH) LIPQOH
    FROM PRODDTA.F41021
   WHERE 1 = 1 --You can put your where clause here
GROUP BY LIMCU, LIITM
ORDER BY DBMS_RANDOM.VALUE
   FETCH FIRST 5 ROWS ONLY;