Re: Obtains rows 10-20 in a SELECT with ORDER BY!

From: Rod Corderey <Lane_Associates_at_compuserve.com>
Date: 1998/05/26
Message-ID: <356AFFB7.C0530667@compuserve.com>#1/1

Fidel,

you may be able to use something like this

select empno, line_no
from ( select empno, rownum line_no

       from ( select empno
              from emp
              group by empno
            )
     )

where line_no between 10 and 20
;

This is based on the inherent ordering of group by, so the group by is placed against the primary key of the table being selected from. The second in line view is to convert rownum from a pseudo column to a real value suitable for use in the between in the outer query.

Bear in mind that if the data set of the innermost query was large, eg the whole table ,then the query could be very performance degrading and resource hungry.

If your objective is to obtain an arbitary set, bearing in mind that "rows 10 to 20" have no real meaning in terms of relative position in the data set, then there are alternatives that are less perfomance hungry.

Regards

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.lane-Associates.com

Fidel Cacheda Seijo wrote:
>
> Hello,
>
> I'm newbie, but I think this is not a very easy question. I've been
> thinking about it some time, but I can't get any solution.
>
> The problem is easy: in a SELECT that returns 100 rows (for example), I
> just need rows from 10 to 20. This is easy to do, but the problem is
> that I need that the query performs first the ORDER BY, and then obtain
> the rows I want.
>
> Any idea??
>
> FY
Received on Tue May 26 1998 - 00:00:00 CDT