ORDER BY is ignored during INSERT INTO

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

ORDER BY is ignored during INSERT INTO

André Borchert
Hello,

I try to copy one table into a second identical one. Once the second table
is created I want to move the content over sorted by ASC.

The issue is that the ORDER BY statement gets ignored during a INSERT INTO:

INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE
CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC

When the SELECT query is executed alone, the content is sorted fine as
expected.

Andre
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ORDER BY is ignored during INSERT INTO

Keith Medcalf

Which version of SQLite3 are you using?  Tip of trunk seems to work correctly ...

SQLite version 3.30.0 2019-08-24 20:18:04
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> create table y(y);
sqlite> insert into x values (3),(2),(4),(1);
sqlite> insert into y select x from x order by x asc;
sqlite> select * from x;
3
2
4
1
sqlite> select * from y;
1
2
3
4

Can you post the output of EXPLAIN INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of André Borchert
>Sent: Sunday, 25 August, 2019 15:10
>To: [hidden email]
>Subject: [sqlite] ORDER BY is ignored during INSERT INTO
>
>Hello,
>
>I try to copy one table into a second identical one. Once the second
>table
>is created I want to move the content over sorted by ASC.
>
>The issue is that the ORDER BY statement gets ignored during a INSERT
>INTO:
>
>INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE
>CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC
>
>When the SELECT query is executed alone, the content is sorted fine
>as
>expected.
>
>Andre
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ORDER BY is ignored during INSERT INTO

Simon Slavin-3
In reply to this post by André Borchert
On 25 Aug 2019, at 10:09pm, André Borchert <[hidden email]> wrote:

> I try to copy one table into a second identical one. Once the second table is created I want to move the content over sorted by ASC.

It's worth noting here that the rows of a table do not have any order in SQL.  A table is a collection of rows, like marbles in a bag.  It is quite correct for any SQL engine to do this:

SELECT name,phone FROM contacts; --> all rows appear
SELECT name,phone FROM contacts; --> same rows in different order

Unless you specify a sort order using ORDER BY you cannot complain about the order SQL returns your rows.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] ORDER BY is ignored during INSERT INTO

Hick Gunter
In reply to this post by André Borchert
Why would you want to do this?

If you require a SELECT to return rows in a certain order, you need to specify ORDER BY on the SELECT statement. And not rely on ascending insert time or any other visitation order effect.

Additionally - unless specific precautions are taken - sorted insert results in a half empty tree structure.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von André Borchert
Gesendet: Sonntag, 25. August 2019 23:10
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] ORDER BY is ignored during INSERT INTO

Hello,

I try to copy one table into a second identical one. Once the second table is created I want to move the content over sorted by ASC.

The issue is that the ORDER BY statement gets ignored during a INSERT INTO:

INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE CompanyDetails.ID > 0 ORDER BY CompanyDetails.RIC ASC

When the SELECT query is executed alone, the content is sorted fine as expected.

Andre
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users