Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!j72g2000cwa.googlegroups.com!not-for-mail
From: "Charles Hooper" <hooperc2000@yahoo.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: restrict the amount of a select's result set
Date: 12 Dec 2006 03:56:39 -0800
Organization: http://groups.google.com
Lines: 64
Message-ID: <1165924599.522798.82130@j72g2000cwa.googlegroups.com>
References: <4gnheaqyg2zn.1hspk5ufc9o5h$.dlg@40tude.net>
NNTP-Posting-Host: 65.118.7.2
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1165924605 13605 127.0.0.1 (12 Dec 2006 11:56:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 12 Dec 2006 11:56:45 +0000 (UTC)
In-Reply-To: <4gnheaqyg2zn.1hspk5ufc9o5h$.dlg@40tude.net>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: j72g2000cwa.googlegroups.com; posting-host=65.118.7.2;
   posting-account=ytcoAwwAAADhCs0M3G1mFO5tqSfx4ge9
Xref: usenetserver.com comp.databases.oracle.misc:244839
X-Received-Date: Tue, 12 Dec 2006 06:56:46 EST (text.usenetserver.com)

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.