db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4) "order by" is not supported for "insert ... select"
Date Wed, 16 May 2007 14:40:17 GMT

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

Bryan Pendleton commented on DERBY-4:

Hi Bernt, thanks for the feedback. I believe I understand your concerns.

I am *not* proposing that the ORDER BY should have any affect on
subsequent SELECT statements. Such a behavior would be indeed wrong.

I am only proposing that the ORDER BY should affect the SELECT that is
part of the INSERT statement itself; that is, that it should affect the order in
which the INSERT statement processes the rows that the SELECT selects.

Note that in Christian's example (and in the tool I'm trying to support), there
is an auto-generated column. The database will automatically generate
new values for this column as the rows are inserted.

The point of this feature is to allow the user to ensure that, *if the rows are
later selected and ordered by the auto-generated column*, then they will
appear in the same order as they appeared during the INSERT.

Thus subsequent SELECT statements *do* have an ORDER BY, but it
is an ORDER BY on the autogenerated column, not on the original columns
of the source data for the INSERT ... SELECT ... ORDER BY.

Does this explanation make more sense?

> "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.

View raw message