| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: order by in a view Help Please
A copy of this was sent to "Mujahid Hamid" <mujahid_at_pharmco.demon.co.uk>
(if that email address didn't require changing)
On Mon, 6 Jul 1998 16:19:24 +0100, you wrote:
>Dear all,
>
>I am trying to use order by in a view but I get Ora 00933 (sql not properly
>ended)
>
>When I use the following I get errors:
>for example
>
> 1.Create view test
> select name, address from customer
> order by name
>2. create table customer_1 as select * from customers
> order by name,
>3. insert into customer select * from old_customer
> order by name
>
order bys cannot be used in a create view statement. A view is a table of sorts -- tables contain unsorted collections of rows. If you want the data sorted on output, you must put the order by in the select statement. In relational databases, data on disk is never ordered, a view is just a (virtual) table and hence is unordered.
One 'trick' way to do it would be:
SQL> l
1 create view emp_view
2 as
3 select ename, empno from emp
4* group by ename, empno, rowid
If you group by want you want to sort on, and then group by on everything else (and if need be, include a unique column to prevent duplicates from being group'ed out), it'll be sorted (probably -- there is no written gaurantee that group by will sort, especially with parallel operations)
Warning -- the group by may be much more expensive then the order by would be...
>how can I create a sorted table from an unsorted table ?
>
>Thanks for you help
>
>Mujahid
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jul 06 1998 - 11:23:05 CDT
 |
 |