Re: Is it possible to get a subset of a result set?

From: Jay Weiland <jay_at_pixar.com>
Date: Tue, 21 Sep 1999 09:02:36 -0700
Message-ID: <37E7AC1C.56D4911B@pixar.com>

Hey Steven,

     Although this one can be slow, it does provide a solution for you:

SQL> desc temp_emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMP_NUM                                  NUMBER
 EMP_NAME                                 VARCHAR2(20)

SQL> select * from temp_emp;

  EMP_NUM EMP_NAME

--------- --------------------
      101 Amy
      102 Bob
      103 Carl
      106 Dave
      104 Frank
      105 Eric
      107 Greg
      108 Hana
      109 Ian
      110 Jay!!!

10 rows selected.

SQL> select employee, rank
  2 from (select emp1.emp_name employee, count(*) rank

  3            from temp_emp emp1,
  4                 temp_emp emp2
  5           where emp1.emp_name >= emp2.emp_name
  6          group by emp1.emp_name)

  7 where rank between 4 and 7;

EMPLOYEE                  RANK
-------------------- ---------
Dave                         4
Eric                         5
Frank                        6
Greg                         7

Hope this helps!

Jay!!!

Steven Hill wrote:

> If an _ordered_ query produces a result set of 10,000 rows, for
> example, is possible to retrieve a subset, say, rows 2500 to 3000
> without reading through the first 2499 rows of the result set?
>
> In Oracle, I'd like to use:
>
> select * from employee
> where rownum >= 2500 and rownum <=3000
> order by lastname
>
> Unfortunately, the order by clause is done after the row range is
> retrieved. Ideally, I'd like the solution to use a single select
> statement and not Java or PL/SQL code. Is this possible?
>
> --
> Steven Hill
Received on Tue Sep 21 1999 - 11:02:36 CDT