Home » RDBMS Server » Server Administration » selecting N rows from a table
selecting N rows from a table [message #374816] Wed, 04 July 2001 08:58 Go to next message
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?
Re: selecting N rows from a table [message #374839 is a reply to message #374816] Thu, 05 July 2001 15:07 Go to previous messageGo to next message
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;
Re: selecting N rows from a table [message #374848 is a reply to message #374839] Fri, 06 July 2001 02:55 Go to previous messageGo to next message
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?
Re: selecting N rows from a table [message #374862 is a reply to message #374848] Sun, 08 July 2001 06:38 Go to previous message
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.)
Previous Topic: Re: Sql Loader and errorlevels for NT?
Next Topic: slow in insertion
Goto Forum:
  


Current Time: Mon Dec 23 19:32:03 CST 2024