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

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

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: 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.

        Attachments

        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

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: