Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Insert from another table...Create with ORDER BY
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.htmlReceived on Fri Aug 08 1997 - 00:00:00 CDT