| rownum and order by [message #9469] |
Thu, 13 November 2003 01:04  |
SANDY
Messages: 60
Registered: April 1998
|
Member |
|
|
Hi,
I have a functionality by which in first page i have to display 10 articles and in second page i have to display from 11 to end.
in my second page i have to write a query to retrive from database from 11 th record also the query has join of multiple tables and also "order by" clause.
i am attaching the sql query, which seems to be working but wanted to confirm if this is correct or any other way of doing it
SQL Query
---------------
SELECT rn,content_id , TITLE,display_datetime FROM
(SELECT ROWNUM rn, content_id , TITLE,display_datetime FROM (
SELECT b.content_id , b.TITLE , b.display_datetime
FROM CONTENT b,CAT c, TYPE d
WHERE b.type_id=d.type_id
AND d.type_name IN('type1','type2')
AND c.cat_id=006
AND b.content_id = c.content_id
ORDER BY b.display_datetime DESC))
WHERE rn BETWEEN 11 AND 20
Thanks in adance
sandy
|
|
|
|
| Re: rownum and order by [message #9470 is a reply to message #9469] |
Thu, 13 November 2003 01:53  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Seems fine, but here's an alternative for a similar query:
Select rn
, ename
, empno
, dname
From ( Select rownum rn
, ename
, empno
, dname
From ( Select rownum rn
, e.ename
, e.empno
, d.dname
From emp e
, dept d
Where d.deptno = e.deptno
Order by d.dname, e.ename
)
)
Where rn between 6 and 10
/
<B>
Select rn
, ename
, dname
From ( Select rank() over ( order by d.dname, e.ename) rn
, e.ename
, e.empno
, d.dname
From emp e
, dept d
Where d.deptno = e.deptno
)
where rn between 6 and 10
/</B>
I believe the second version is faster in larger environments. But you have to make sure that there's no duplicates in the order by clause of the rank() function.
MHE
|
|
|
|
| Re: rownum and order by [message #9473 is a reply to message #9469] |
Thu, 13 November 2003 10:29 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The optimal structure for a paging result set is:
select *
from (select rownum rn, t.*
from (<query with order by clause>) t
where rownum <= :max_row_number)
where rn >= :min_row_number;
This allows the optimizer to use a COUNT STOPKEY which it cannot with the 'BETWEEN min and max' approach.
Your query would look something like:
select *
from (select rownum rn,
t.*
from (select b.content_id, b.title, b.display_datetime
from content b,
cat c,
type d
where b.type_id = d.type_id
and d.type_name in ('type1', 'type2')
and c.cat_id = 6
and b.content_id = c.content_id
order by b.display_datetime desc) t
where rownum <= :max_row_number)
where rn >= :min_row_number;
You first call would pass 1 and 10, second call 11 and 20, etc.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
