-
How can I get certain records from a table
how can I write a query to get records from n to m.
example
SELECT column_a
FROM table_name
WHERE rownum BETWEEN 10 and 20;
order by column_a
I am trying to get records between 10 and 20 only.
but the ROWNUM DOESN'T WORK IN THIS CASE.
hope this makes it more clear.
Thanks
Last edited by ocpdude; 12-03-2004 at 12:16 PM.
-
Code:
select * from table_name
where field_name between 10 and 20;
-
Originally posted by waitecj
Code:
select * from table_name
where field_name between 10 and 20;
Or are you asking for?
Code:
SELECT *
FROM table_name
WHERE rownum BETWEEN 10 and 20;
-
Vague answer given to a vague question
-
SELECT *
FROM table_name
WHERE rownum BETWEEN 10 and 20;
but the rownum doesn't work
-
What do you see when you run this query then?
-
1 select agreement_id from
2 br_agreement
3 where rownum between 1 and 5
4* order by agreement_id
SQL> /
AGREEMENT_ID
---------------
RW97979748
0012471TIH
030710305N
030720304N
06-06-03
SQL> ed
Wrote file afiedt.buf
1 select agreement_id from
2 br_agreement
3 where rownum between 10 and 20
4* order by agreement_id
SQL> /
no rows selected
as you can see from the above query anything greater than 1 returns
nothing.
Thanks,
-
Re: How can I get certain records from a table
Originally posted by ocpdude
WHERE rownum BETWEEN 10 and 20
Will NEVER return any thing.
You need something like:
Code:
select * from
(select a.*, rownum rnum
from a
where rownum =< 20)
where rnum >= 10
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM emp
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
Last edited by DaPi; 12-03-2004 at 12:38 PM.
-
Come to think of it, if you want meaningful results you'd better order by something, otherwise the result is effectively random:
Code:
select * from
(select a.*, rownum rnum
from
(select * from my_table
order by something) a
where rownum =< 20)
where rnum >= 10
-
Thanks
I tried your first query with the ORDER BY and it worked fine.
select COL_1 from
(select a.COL_1, rownum rnum
from TABLE a
where rownum <= 20
OREDER BY COL_1)
where rnum >= 10
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|