Re: Limiting Returned Rows In Select Statement

From: Richard Hoffbeck <rwh_at_visi.com>
Date: 1998/02/25
Message-ID: <MPG.f5dfb2e155b6a1989683@fw2.mwcia.org>#1/1

[This followup was posted to comp.databases.oracle.server and a copy was sent to the cited author.]

In article <34f3098c.85067550_at_ntserv02>, matthew_at_nospam.mattshouse.com says...
> OK, I now understand what you're saying. But how do I get the next
> 15? Is this legal?
>
> select * from Imagedata where RowNum > 15 and RowNum < 31
>
> I can't get this one to work.

This can get messy and the only way I've found to do it is to use an embedded view, i.e. something like:

SELECT *

     FROM ( SELECT ID, 
                   NAME, 
                   STREET,
  	            CITY,
                   STATE,
                   rownum R         
             FROM  mytable
          )
    WHERE R > 15
      AND R <= 30

And in the inner view, you do have to explicitly list all of the fields that you want to select. SELECT *, rownum R doesn't work.

Hope it helps!

--rick Received on Wed Feb 25 1998 - 00:00:00 CST