Derby
  1. Derby
  2. DERBY-4

"order by" is not supported for "insert ... select"

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      When filling a table with "insert ... select ...", "order by" cannot be specified.

      There is no 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.

      1. derby-4_dhw.diff
        38 kB
        Dag H. Wanvik
      2. derby-4_dhw.stat
        1 kB
        Dag H. Wanvik
      3. insertOrderBy_v2.diff
        6 kB
        Bryan Pendleton
      4. insertOrderBy_v3.diff
        9 kB
        Bryan Pendleton
      5. insertOrderBy.diff
        4 kB
        Bryan Pendleton
      6. samples.ij
        2 kB
        Bryan Pendleton
      7. samples.ij
        1 kB
        Bryan Pendleton

        Issue Links

          Activity

          Christian d'Heureuse created issue -
          Mike Matrigali made changes -
          Field Original Value New Value
          Component/s SQL [ 11408 ]
          Description 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.

          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.

          Environment
          Bryan Pendleton made changes -
          Assignee Bryan Pendleton [ bryanpendleton ]
          Bryan Pendleton made changes -
          Link This issue is related to DERBY-581 [ DERBY-581 ]
          Bryan Pendleton made changes -
          Attachment insertOrderBy.diff [ 12357481 ]
          Bryan Pendleton made changes -
          Attachment samples.ij [ 12357707 ]
          Attachment insertOrderBy_v2.diff [ 12357706 ]
          Bryan Pendleton made changes -
          Attachment samples.ij [ 12357943 ]
          Attachment insertOrderBy_v3.diff [ 12357942 ]
          Bryan Pendleton made changes -
          Assignee Bryan Pendleton [ bryanpendleton ]
          Dag H. Wanvik made changes -
          Issue Type New Feature [ 2 ] Improvement [ 4 ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-4397 [ DERBY-4397 ]
          Dag H. Wanvik made changes -
          Attachment derby-4_dhw.diff [ 12423484 ]
          Attachment derby-4_dhw.stat [ 12423485 ]
          Christian d'Heureuse made changes -
          Description 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.

          When filling a table with "insert ... select ...", "order by" cannot be specified.

          There is no 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.

          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-4442 [ DERBY-4442 ]
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 37477 ] Default workflow, editable Closed status [ 12799134 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Christian d'Heureuse
            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development