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

Interesting solution. Any idea how slow is "slow" for a result set, say 10000 rows are returned from the subquery in the from clause, min_rank is 8000 and max_rank is 8500? Assume emp_name is indexed.

Steven Hill

Jay Weiland wrote:

> 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

--
Steven Hill,
Computer Catalyst, Inc.
Toronto, Canada

(please take out 'remove' from my address when sending an email) Received on Tue Sep 21 1999 - 16:48:21 CDT