Interested to Buy Any Domain ? << Click Here >> for more details...
SELECT * FROM
(SELECT TITLE FROM MOVIE ORDER BY RANK DESC)
WHERE ROWNUM > 4;
when i run the above query .it produces output as NO ROWS
SELECTED.why ?plz any one help me
- 6 Answers
- 14251 Views
- TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / rachita
This is because you cannot use > operator with Top-N
Analysis.The operator which is used is only < less-then and
<= less-then-equal-to .You can also use = operator but only
for retrieving the top most value .
| Is This Answer Correct ? | 13 Yes | 1 No |
Answer / vamsi krishna
the reason behind this is rownum will be generated only
after selecting the row from the table.hence when you are
using inline views ,the outer select statement is having a
rownum and inner select statement is having a rownum,so due
to the ambiguity your query is not working.
i give the following change to ur query.
SELECT * FROM
(SELECT rownum as r1,TITLE FROM MOVIE )
WHERE r1 > 4;
| Is This Answer Correct ? | 5 Yes | 4 No |
Answer / dinesh a.
you can't use rownum > 4 ( you can use only rownum <
clause). not in inner query or inline view even in normal
query.
select empno from emp where rownum > 5 ..
The output you will get NO ROWS always ....
so the magic is only order by clause asc or desc pls see
below
suppose we have a table x with one column col1 and values
are 1 to 10...
SELECT rownum,rank,col1 FROM
(SELECT rownum rank,col1 FROM x ORDER BY col1 desc)
WHERE ROWNUM <= 6 /* last six rows mean > 4 */
/
ROWNUM RANK COL1
------ ---------- ----------
1 10 10
2 9 9
3 8 8
4 7 7
5 6 6
6 5 5
1 SELECT rownum,rank,col1 FROM
2 (SELECT rownum rank,col1 FROM x ORDER BY col1 asc)
3* WHERE ROWNUM <= 4 /* first 4 rows */
SQL> /
ROWNUM RANK COL1
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / obed
You donot use '>4' for comparing
you must use '<=4'
if u used >4 its will not return any row because there in
no row is select >
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mohan
select * from movie where rownum>4(select title from movie
rank()over(order by title desc);
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / imran raza
ORDER BY Clause inside Inline View is not permissible.So in
query (SELECT TITLE FROM MOVIE ORDER BY RANK DESC)it should
be only SELECT TITLE FROM MOVIE .
Moreover Order by can be used by specifying the name (
selected row) of coloumn in order by clause.
Instead of names numbers can also be used.ie Order by 1.
| Is This Answer Correct ? | 1 Yes | 1 No |
Explain the concept of the DUAL table.
How to bring a tablespace online?
What is ceil and floor in oracle?
Difference between NO DATA FOUND and %NOTFOUND?
what are Triggers?
I have my backup RMAN script called backup_rman.sh. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
how to produce numbers from 1 to 10 using dual table
There are three tables : E : EID,ENAME D : DID,DNAME empdept : eid, did select the employees who doesn't belong to any dep
how to find count rows in table without count function?
When do we use group by clause in a sql query?
How to define a data field as not null?
How to change system global area (sga) in oracle?
Oracle General 
