[jira] Commented: (DERBY-4) "order by" is not supported for "insert ... select"

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496056 ] 

Bryan Pendleton commented on DERBY-4:
-------------------------------------

I am interested in adding this feature to Derby; I find that I have the 
same need that Christian identified.

I understand the concerns that Jeff raised about this being a 
non-standard language extension. However, Christian's usage
seems quite reasonable to me (and in fact matches my usage),
and this extension is commonly supported by many other databases
so we are not really breaking new ground here.

I've been working on a patch to add this support and hope to post it
soon for discussion, once I've seen how the testing goes.


> "order by" is not supported for "insert ... select"
> ---------------------------------------------------
>
>                 Key: DERBY-4
>                 URL: https://issues.apache.org/jira/browse/DERBY-4
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Christian d'Heureuse
>         Assigned To: Bryan Pendleton
>            Priority: Minor
>
> When filling a table with "insert ... select ...", "order by" cannot be specified.
> There is not method to copy a table sorted into another table (except using export/import). This would be useful to optimize performance for big tables, or to create identity values that are ascending (related to another column).
> Example:
> create table temp1 (
>    s varchar(10));
> insert into temp1 values 'x','a','c','b','a';
> create table temp2 (
>    i integer not null
>       generated always as identity
>       primary key,
>    s varchar(10));
> insert into temp2 (s)
>    select s from temp1 order by s;
> --> Error: "order by" is not allowed.
> -- trying to use "group by" instead of "oder by":
> insert into temp2 (s)
>    select s from temp1 group by s;
> select * from temp2;
> --> "group by" did not sort the table.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.