Re: Limit a result list

Do it like this:

  select * from
  (
    SELECT t.*, ROWNUM rn FROM tablename t WHERE something     ORDER BY entrydate
  )
  where rn between 30 and 40;

It does not work without the subselect, because ROWNUM would never exceed 1:

Consider the following select:

  SELECT t.*, ROWNUM rn FROM tablename t WHERE something ORDER BY   WHERE ROWNUM between 30 and 40;

Fetch first row. Rownum between 30 and 40? No, it is 1. Fetch second row. Rownum between 30 and 40? No, it is STILL 1 (because this would be the FIRST row to be fetched). Fetch third row. Rownum between 30 and 40? No, it is STILL 1.

Thus, there would never be a row returned. You have to select the rownum first and THEN subquery and "where" it.

hth
Alex

oracle_at_banholzer.de tipperte...
> I have the following problem:
>
> How can I get the results (for example 30 - 39) from a select statement?
> The result list should by ordered by a date.
>
> With MySQL I did it like :
>
> SELECT * FROM tablename WHERE something ORDER BY entrydate LIMIT 30, 10
Received on Tue Dec 03 2002 - 09:14:03 CST