Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Insert from another table...Create with ORDER BY

Re: Insert from another table...Create with ORDER BY

From: Eric & Carole Kamradt <kamradt_at_inconnect.com>
Date: 1997/08/08
Message-ID: <33EB0D10.CE57AF05@inconnect.com>#1/1

A small trick I just figured out.
You don't seem to be able to use the ORDER BY clause in a CREATE TABLE x AS SELECT ...
or in a INSERT INTO TABLE x SELECT ...
but you can use a GROUP BY
so if you use a set of columns that is unique like (txnum, txtotal, counter_field)
then you can say
CREATE TABLE x
AS
  SELECT *
   FROM sourceTable
  GROUP BY txnum, txtotal, counter_field; or
INSERT INTO x
  SELECT *
   FROM sourceTable
  GROUP BY txnum, txtotal, counter_field;

see if that doesn't work for you

Good Luck
Eric Kamradt

eberry_at_dstcorp.com wrote:

> I am doing this same thing in SQL Server 6.5, however I need to
> translate
> my code into Oracle. This statement works in SQL 6.5, but not in
> Oracle,
> that I know, but is there a workaround for this.
>
> I have this table (txnum, txtotal, counter_field), I want to create a
> table with the txtotal ordered desc. From there I will need to assign
>
> row number to counter_field, after this, I will run some declare
> statments
> to break the number of physical records into 10% records, 1700 records
>
> numbered 1-1700 by rownum will put 10 declare fields into 170 record
> segments.
>
> There has got to be a way to do this in Oracle. Any assistance in
> this would greatly help. (The SQL 6.5 code to do this is avail if
> this is
> not clear - email me if you'd like further explanation.)
>
> Thanks!
>
> Subject: Insert from another table...
> From: lesliet_at_u.washington.edu (L. Tseng)
> Date: 1997/01/17
> Message-Id: <5bp2pv$n4l_at_nntp1.u.washington.edu>
> Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools
>
> [More Headers]
>
> Orcale does not take ORDER BY clause in the following insert
> syntax. Do you know why? Any suggestion on workaround?
> Thanks,
> Leslie
>
> insert into table_a (col1, col2,col3)
> select x, y, z from table_b
> order by x
>
> -------------------==== Posted via Deja News
> ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

--
Thanks for your time

Eric Kamradt
hm: (801) 567-0912
mailto: kamradt_at_inconnect.com
http://www.inconnect.com/~kamradt/resume.html
Received on Fri Aug 08 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US