OraFAQ Forum: Server Administration » selecting N rows from a table

Home » RDBMS Server » Server Administration » selecting N rows from a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

selecting N rows from a table [message #374816]

Wed, 04 July 2001 08:58

Joni Salonen
Messages: 3
Registered: July 2001

Junior Member

I need to select the first N rows at an offset of M. It would be simple with MySQL's LIMIT and OFFSET but Oracle doesn't seem to have this functionality - or has it? How would you solve this problem? I really don't like going through the entire result in PHP and removing the unwanted rows "by hand"...

If the first problem is easily solved, a possible solution for the offset-part could be selecting M+N rows and taking the first M rows out with MINUS, am I right?

Report message to a moderator

Re: selecting N rows from a table [message #374839 is a reply to message #374816]

Thu, 05 July 2001 15:07

Sudhakar Atmakuru
Messages: 58
Registered: May 2001

Member

Try the SELECT statement with the seudo column ROWNUM;

Ex: to print first 10 rows from the table

SELECT * FROM table_name WHERE ROWNUM <= 10;

Report message to a moderator

Re: selecting N rows from a table [message #374848 is a reply to message #374839]

Fri, 06 July 2001 02:55

Joni Salonen
Messages: 3
Registered: July 2001

Junior Member

Suppose you had an order by in there. That's going to mix the row numbering which isn't very nice.

Is there any way to do it without using silly constructs like

SELECT field1,field2,... FROM (
   SELECT field1,field2,...,ROWNUM rno FROM (
        select field1,field2,... from table
        order by fieldi ASC
   )
) WHERE rno BETWEEN lowerbound AND upperbound;

or PL/SQL?

BTW: if I use the >= and > operators with rownum in select you no rows seem to be returned. Same thing with BETWEEN and lowerbound greater than 1. Why?

Report message to a moderator

Re: selecting N rows from a table [message #374862 is a reply to message #374848]

Sun, 08 July 2001 06:38

Joni Salonen
Messages: 3
Registered: July 2001

Junior Member

I don't think using ROWID would be any good. I'm not interested in the actual positions of the rows in the table. I'm interested in the position of the rows in the result.

Let's take a hypotetical example: suppose you were writing an internet forum system where you could sort the messages. You want to show only 20 messages on one html page. The messages (there can be tens, even hundreds of thousands of them) are stored in a database. How do you efficiently select eg. the 20 latest messages when they aren't in any particular order what comes to ROWNUM?

You could have something like

SELECT * FROM {
  SELECT * FROM forum_messages
  ORDER BY sent_date
} WHERE ROWNUM <= 20;

But that seems hardly efficient.

Well, suppose the user wants to go to the second page: you'll need a query like

SELECT * FROM {
  SELECT ROWNUM rnum,[all needed fields] FROM {
    SELECT * FROM forum_messages
    ORDER BY sent_date
  } WHERE ROWNUM <= 40
} WHERE rnum > 20;

- or -

SELECT * FROM {
  SELECT * FROM forum_messages
  ORDER BY sent_date
} WHERE ROWNUM <= 40
MINUS
SELECT * FROM {
  SELECT * FROM forum_messages
  ORDER BY sent_date
} WHERE ROWNUM > 20;

(having "rownum is between 21 and 40" or "rownum > 20 and rownum <= 40" as the where condition doesn't seem to work)

Do you really need 3-4 queries to accomplish this rather simple feature? If the table will contain hundreds of thousands of rows first selecting all of them and continuing from it just seems so slow. (Is it actually? I don't know; I don't have very much experience of Oracle and databases in general.)

Report message to a moderator

Previous Topic:	Re: Sql Loader and errorlevels for NT?
Next Topic:	slow in insertion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 06 17:03:06 CDT 2024