| sql help [message #372241] |
Mon, 22 January 2001 07:35  |
Ravi Kiran
Messages: 5
Registered: January 2001
|
Junior Member |
|
|
please help me,
1. i created a table t1 from view v1. now if i drop v1 what happens to table t1?
2. can we use order by in a subquery?
|
|
|
|
| Re: sql help [message #372246 is a reply to message #372241] |
Mon, 22 January 2001 08:43  |
Balazs VISSY
Messages: 17
Registered: January 2001
|
Junior Member |
|
|
1. i created a table t1 from view v1. now if i drop v1 what happens to table t1?
I don't understand well what you've done, but I guess it is something like
a) Created the same structure as the view has
b) Filled in with data from the view
In both cases you can drop the view when you like. The table is a permament object, so removing anything doesn't effect it (unless there is a foreign key constraint between them). However, the other way isn't true: after creating a view based on a table, it requires the table to work properly, for a view is a "virtual, filtered table".
2. can we use order by in a subquery?
No, but there is no reason to do that. A subquery is for receiving data inside a filter clause, so there is no way to display directly the values. This makes ordering useless.
I hope these remarks help
Balage
|
|
|
|
| Re: sql help [message #372251 is a reply to message #372246] |
Mon, 22 January 2001 12:25 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
One thing which is useful is including an ORDER BY in the following:
create table xyz as
select *
from emp
order by emp_no;
Now, when you create an index of PK on emp_no in the new table there is a benefit.
|
|
|
|
| You can use order by in subquery [message #372253 is a reply to message #372241] |
Mon, 22 January 2001 13:23 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
Ofcourse you can use order by inside a subquery...
This functionality is included from oracle 8.1.x
.
And it is useful in some cases
for instance if you want to rank employees based on their sal...
then you can do
select rownum rank, empno, ename, sal from
(select empno, ename, sal from emp
order by sal desc);
RANK EMPNO ENAME SAL
------ --------- ---------- ---------
1 7839 KING 5000
2 7788 SCOTT 3100
3 7902 FORD 3000
4 7566 JONES 2975
5 7698 BLAKE 2850
6 7782 CLARK 2450
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
