Select rows from an ordered resultset

[mesuj]

Hi
My requirement is i need to fetch rows between 2 given rownum like say 10 to 20 , 30 to 40 etc.

The query which i used is

select * from(
select x , rownum record_num from table1
)result_table
where record_num between 10 and 20

The problem now is i want to sort by x and then pick records by the ordered rownumber wise.
i.e Order by x first. You get a set of result set which is ordered and then select records between 10 and 20.

I tried
select * from (
select x , rownum record_num from table1 order by x
)result_table
where record_num between 10 and 20.

This doesn't work as rownum is alloted before order by.
Now how do i proceed.
Any help would be much appreciated.
thanks

 

[karluk]

I would try an extra level of nesting. Something like

select x, record_num from
(
select x, rownum record_num from
(
select x from table1 order by x
)
)
where record_num between 10 and 20;

 

[sfvb]

I can't really tell what you trying to ask. From what I understand, the statement you posted should work. Here are some various outputs using rownum on the same set of data. I hope one of them is what you're looking for.

SELECT   x, rownum record_num
FROM     table1;

X RECORD_NUM
- ----------
e          1
c          2
a          3
b          4
f          5
d          6

SELECT   * 
FROM     (  SELECT   x, rownum record_num
            FROM     table1
         )
WHERE    record_num BETWEEN 2 AND 4;

X RECORD_NUM
- ----------
c          2
a          3
b          4

SELECT   * 
FROM     (  SELECT   x, rownum record_num
            FROM     table1
            ORDER BY x
         )
WHERE    record_num BETWEEN 2 AND 4;

X RECORD_NUM
- ----------
b          2
c          3
d          4

SELECT   * 
FROM     (  SELECT   x, rownum record_num
            FROM     table1
         )
WHERE    record_num BETWEEN 2 AND 4
ORDER BY x;

X RECORD_NUM
- ----------
a          3
b          4
c          2

Steve

 

[mesuj]

karluk - that query did work. Thanks for u r help.
Steve - Did you try executing the
SELECT *
FROM ( SELECT x, rownum record_num
FROM table1
ORDER BY x
)
WHERE record_num BETWEEN 2 AND 4;

query. This doesn't work. The results which you have is right. May be that was a coincedence. This allots the rownum first and then orders it. so when you execute the where clause it still picks up the exact 2 to 4 records and not the ordered one.
Thanks.

 

[mesuj]

But this solutions works only in 8.1 version and not in 8.0.

 

[sfvb]

mesuj,

Yes I executed the statement before I posted it. It works fine on the table I created with PO 8.1.7. I did a little more checking after you posted and I get this:

SELECT   x, rownum record_num
FROM     table1;

X RECORD_NUM
- ----------
e          1
c          2
a          3
b          4
f          5
d          6

SELECT   x, rownum record_num
FROM     table1
ORDER BY x;

X RECORD_NUM
- ----------
a          1
b          2
c          3
d          4
e          5
f          6

I get the same results on any table that does't have a FK constraint in it. If I try it on a table with a FK constraint, I get the results you are talking about. I'll add a column to this table and put a FK contraint on it, tomorrow, to see if that's what's causing the discrepency.

Steve

 

[mesuj]

But you forgot the most important between clause. Thats where i'm struck.Thanks for the pains

 

[sfvb]

mesuj,

SELECT   * 
FROM     (  SELECT   x, rownum record_num
            FROM     table1
            ORDER BY x
         )
WHERE    record_num BETWEEN 2 AND 4;

This seems to work if there is an index on the column in question.

Steve