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

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.

Am 12 Dec 2006 03:56:39 -0800 schrieb Charles Hooper:

> 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
>

Thanks, that solves exactly the problem.

--
Norbert