SQL Query - Rownum [message #7568] |
Sun, 22 June 2003 22:29 |
VB
Messages: 6 Registered: June 2003
|
Junior Member |
|
|
Hi All,
I am unable to get the logic behind these two queries.
Answers should have been same logically but Oracle gives different results with these two queries.
Please explain.
Queries :
SQL> select ename from emp where rownum in (1,2);
ENAME
----------
JAMES
ALBERT
SQL> select ename from emp where rownum in (select rownum from emp where rownum in (1,2));
ENAME
----------
JAMES
JAMES
Thanks,
Vibhu Bahl
|
|
|
Re: SQL Query - Rownum [message #7573 is a reply to message #7568] |
Mon, 23 June 2003 03:45 |
Mahesh
Messages: 90 Registered: January 2001
|
Member |
|
|
select a.y,b.x,a.ename from (select rownum y,ename from emp ) a, (select rownum x from emp where rownum < 4 ) b;
Y X ENAME
------ ---------- ----------
1 1 PPPP
2 1 ALLEN
3 1 WARD
4 1 JONES
5 1 mahesh
6 1 mahesh
7 1 CLARK
8 1 SCOTT
9 1 TURNER
10 1 ADAMS
11 1 JAMES
Y X ENAME
------ ---------- ----------
12 1 MILLER
1 2 PPPP
2 2 ALLEN
3 2 WARD
4 2 JONES
5 2 mahesh
6 2 mahesh
7 2 CLARK
8 2 SCOTT
9 2 TURNER
10 2 ADAMS
Y X ENAME
------ ---------- ----------
11 2 JAMES
12 2 MILLER
1 3 PPPP
2 3 ALLEN
3 3 WARD
4 3 JONES
5 3 mahesh
6 3 mahesh
7 3 CLARK
8 3 SCOTT
9 3 TURNER
Y X ENAME
------ ---------- ----------
10 3 ADAMS
11 3 JAMES
12 3 MILLER
U CAN SEE FROM THE ABOVE EXAMPLE THAT IT IS PERFORMING THE CROSS-PRODUCT OF FIRST QUERY WITH THE SECOND QUERY..
THIS IS WHAT I CAN SAY...IF ANY BETTER EXPLANATIONS U CAN SEND ME THE MAIL.
|
|
|