Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4

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

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Minor
    • Resolution: Duplicate
    • None
    • None
    • SQL
    • 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.

      Attachments

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

        Issue Links

          Activity

            People

              Unassigned Unassigned
              chdh@inventec.ch Christian d'Heureuse
              Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: