Re: restrict the amount of a select's result set

Norbert Winkler wrote:
> Hi,
>
> is it possible to restrict a select (with group) that it returns only the
> first 10 records of the result set (counted "after" group by).
>
> --
> Norbert

It helps to post an example of the problem that you are working with, including all SQL statements and the expected output.

For example:
The setup:
CREATE TABLE TABLE1 (X NUMBER(10), Y NUMBER(10), Z NUMBER(10));

INSERT INTO TABLE1 VALUES (1,1,5);
INSERT INTO TABLE1 VALUES (1,3,5);
INSERT INTO TABLE1 VALUES (2,6,1);
INSERT INTO TABLE1 VALUES (5,9,8);

The SQL statement, but I only want to retrieve the first two rows: SELECT
  X,
  MIN(Y) Y,
  MIN(Z) Z
FROM
  TABLE1
GROUP BY
  X;

         X Y Z
========== ==========

	 1	    1	       5
	 2	    6	       1
	 5	    9	       8

If I slide the above into an inline view, I can use ROWNUM to retrieve only the first two rows:
SELECT
  X,
  Y,
  Z
FROM
  (SELECT
    X,
    MIN(Y) Y,
    MIN(Z) Z
  FROM
    TABLE1
  GROUP BY
    X)
WHERE
  ROWNUM<=2;

         X Y Z
========== ==========

	 1	    1	       5
	 2	    6	       1

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Dec 12 2006 - 05:56:39 CST