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

        Issue Links

          Activity

          No work has yet been logged on this issue.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development