| SQL Command that returns only certain rows [message #370757] |
Tue, 01 February 2000 05:45  |
Rene Bauer
Messages: 1
Registered: February 2000
|
Junior Member |
|
|
Hallo,
I would like to do an select with an order by clause that returns only certain rows like 10-20.
I tried a few things like:
select * from tablename where rownum<=20 SELECT ROWNUM<="10" TABLENAME * MINUS COLUMN FROM COLUMN;<BR BY WHERE ORDER>This does not work because SQL complains about the order by. What else can I do?
Ciao,
Rene
|
|
|
|
| Re: SQL Command that returns only certain rows [message #370760 is a reply to message #370757] |
Tue, 01 February 2000 13:35  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo,
You can do this, but in a tricky way.
First, when you use an "ORDER BY" and use the "ROWNUM" in one select,
ex: "SELECT rownum,* FROM tablename ORDER BY column" you will see that the
rownums are no more in Ascending Sequence.
So the solution is that you must see that rows are comming directly in the correct
order. We can do this with a subselect, but here you can not use an order by.
But if you create an index on the column to order by, you can specify a hint, so
oracle will give then in the order of the Index
So the Solution is:
SELECT *
FROM (SELECT /*+ (INDEX_ASC(tab1 index_name_column) */
rownum tabrownum
,tab1.*
FROM tablename tab1)
WHERE tabrownum BETWEEN 10 AND 20
;
You see, it is as easy as that.
Hope this works.
Greetings,
Thierry.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
