Re: Unable to use ORDER BY in an INSERT statement

Sukhvinder Aujla <saujla_at_axion.bt.co.uk> wrote:

>Would the following do what you want?
>CREATE SEQUENCE seq;
 

>INSERT INTO new_table (user_ref, start, revc_id>
> SELECT bt.account_no, '07:30', seq.NEXTVAL
> FROM big_table bt
> WHERE bt.filter = 1;
 

> DROP SEQUENCE seq;

I don't think so. He wants to order the new table. Just selecting without order by doesn't perform an ordering operation. It may seem so by the result, but this is pure coincidence. Try this:

create table x
as select empno, name, dept
from emp
union
select empno, name, dept
from emp;

This will order the table x by the empo column. Group by will also do an ordering, but there are differences in the results with order by (check that first before you use it).

Hope this helps...
* This message was brought to you in stereo (where available) by:

    +-------------------------------------------------+
   / Mike Philippens - Gorinchem - The Netherlands   /
  /              mikephil_at_metropolis.nl             /

+-------------------------------------------------+