Insert all rows from old table into new table but in sorted order

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

Insert all rows from old table into new table but in sorted order

ajay-7
Hello all,

I can insert all rows of existing table into new table having same columns
using query :

 

Insert into NEWTABLE select * from OLDTABLE

 

But I want all rows of NEWTABLE sorted by field No,

So I used query

 

Insert into NEWTABLE select * from OLDTABLE order by no desc

 

But it is not giving me sorted output as new table?

Can you tell me where I am wrong ???

 

Reply | Threaded
Open this post in threaded view
|

Re: Insert all rows from old table into new table but in sorted order

Paul Smith-6

>I can insert all rows of existing table into new table having same columns
>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE', the
results are returned in an undefined order - not necessarily in the order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/


Reply | Threaded
Open this post in threaded view
|

SQLite.NET in-memory database creation

Peter Berkenbosch
In reply to this post by ajay-7
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Stupid question, but how can i create an in-memory database with
SQLite.NET provider? When i use Data Source=:memory: i get an exception
that the specified file isn't found.

Can anyone explain to me how it is done ?

Thanks in advance.

Regards
Peter
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFCw9wA9bwznA9lXw0RAuZLAJ44A+1ZdLPjAyTKQuHnhtRnYgZfjgCgl7Av
dsOIM3vijuNAzPDrD9fjQPs=
=8BhT
-----END PGP SIGNATURE-----
Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

ajay-7
In reply to this post by Paul Smith-6

you misinterpreted my problem,
I want to add all rows of old table into new table but with sorted order
I don't want to fire another query (select * from newtable order by desc no
) to give sorted rows, I want to insert all rows in sorted order into new
table.


-----Original Message-----
From: Paul Smith [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 4:53 PM
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but in
sorted order


>I can insert all rows of existing table into new table having same columns
>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE', the
results are returned in an undefined order - not necessarily in the order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/



Reply | Threaded
Open this post in threaded view
|

Re: Insert all rows from old table into new table but in sorted order

Brad-19
> > You can't do that.

> > The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the

> you misinterpreted my problem,
> I want to add all rows of old table into new table but with sorted order
> I don't want to fire another query (select * from newtable order by desc no
> ) to give sorted rows, I want to insert all rows in sorted order into new
> table.

As the original responder said "You can't do that".  The records may or may not be inserted into the target table in the physical
order you specified on the insert query.  The physical order in the target table depends on how the SQL engine decides to write
them.

The order in which rows are returned from a query that doesn't specify order is undefined, meaning they may be in the order they are
in the table, or some other order that is the result of the SQL engine's optimization.

In other SQL engine's, you can force the physical order of the rows by using what MS SQL Server calls a "clustered" index, which
isn't really an index at all, but rather a physical ordering of the rows in a table.  I haven't seen anything about SQLite
supporting clustered indexes, but you might check the www.sqlite.org website to see if it does or if there are any plans to include
it in the future.

Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

Steve O'Hara
In reply to this post by Paul Smith-6

I might be wrong, but if you don't specify a sort column, you will get the
rows out in PRIMARY KEY order, irrespective of how you loaded the data.
Therefore, you will need to do something a little more interesting with your
loading statement to perhaps exclude the primary key and let the insert
re-generate them.

Just a thought.

Steve

-----Original Message-----
From: sqlite-users-return-6291-sohara=[hidden email]
[mailto:sqlite-users-return-6291-sohara=[hidden email]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



>I can insert all rows of existing table into new table having same columns
>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE', the
results are returned in an undefined order - not necessarily in the order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/






Reply | Threaded
Open this post in threaded view
|

Re: SQLite.NET in-memory database creation

Brad-19
In reply to this post by Peter Berkenbosch
> Stupid question, but how can i create an in-memory database with
> SQLite.NET provider? When i use Data Source=:memory: i get an exception
> that the specified file isn't found.

If you are using the ADOSQLiteDotNet provider from SourceForge, add "New=True" to your connection string.

Reply | Threaded
Open this post in threaded view
|

Re: SQLite.NET in-memory database creation

Brad-19
In reply to this post by Peter Berkenbosch
> > Stupid question, but how can i create an in-memory database with
> > SQLite.NET provider? When i use Data Source=:memory: i get an exception
> > that the specified file isn't found.
>
> If you are using the ADOSQLiteDotNet provider from SourceForge, add "New=True"
> to your connection string.

To clarify, my connection strings for in-memory databases look like this:

"Data Source=:memory:;Version=3;New=True"
Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

ajay-7
In reply to this post by Steve O'Hara

Yaa that's what I wanted to do , So what do you think what could be the
solution for this ?


-----Original Message-----
From: Steve O'Hara [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 5:28 PM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but in
sorted order


I might be wrong, but if you don't specify a sort column, you will get the
rows out in PRIMARY KEY order, irrespective of how you loaded the data.
Therefore, you will need to do something a little more interesting with your
loading statement to perhaps exclude the primary key and let the insert
re-generate them.

Just a thought.

Steve

-----Original Message-----
From: sqlite-users-return-6291-sohara=[hidden email]
[mailto:sqlite-users-return-6291-sohara=[hidden email]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



>I can insert all rows of existing table into new table having same columns
>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE', the
results are returned in an undefined order - not necessarily in the order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/







Reply | Threaded
Open this post in threaded view
|

Re: SQLite.NET in-memory database creation

Peter Berkenbosch
In reply to this post by Peter Berkenbosch
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Berkenbosch wrote:

> Stupid question, but how can i create an in-memory database with
> SQLite.NET provider? When i use Data Source=:memory: i get an exception
> that the specified file isn't found.
>
> Can anyone explain to me how it is done ?
>
> Thanks in advance.
>
> Regards
> Peter


Just found out that there's a bug report on
http://sourceforge.net/tracker/index.php?func=detail&aid=1187987&group_id=94056&atid=606537


- --
+-------------------------------+--------------------------+
: Peter Berkenbosch :   :
: : t: +31 (0) 64 84 61653   :
: PeRo ICT Solutions : f: +31 (0) 84 22 09880   :
: Koemaad 26 : m: [hidden email]     :
: 8431 TM Oosterwolde : w: www.pero-ict.nl       :
+-------------------------------+--------------------------+
: OpenPGP 0x0F655F0D (random.sks.keyserver.penguin.de)   :
+----------------------------------------------------------+





-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFCw+Sb9bwznA9lXw0RAhJ2AJ9XqAjJqVq4F2xr6Bc99Vkzuv6wGQCfe9Co
L5mNRHr4bZTa4yUUZtwQXnw=
=Alav
-----END PGP SIGNATURE-----
Reply | Threaded
Open this post in threaded view
|

Re: SQLite.NET in-memory database creation

Peter Berkenbosch
In reply to this post by Brad-19
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brass Tilde wrote:
>>Stupid question, but how can i create an in-memory database with
>>SQLite.NET provider? When i use Data Source=:memory: i get an exception
>>that the specified file isn't found.
>
>
> If you are using the ADOSQLiteDotNet provider from SourceForge, add "New=True" to your connection string.
>
>
>
I tried that as well, but no succes. I will go into debug-mode :)

- --
+-------------------------------+--------------------------+
: Peter Berkenbosch :   :
: : t: +31 (0) 64 84 61653   :
: PeRo ICT Solutions : f: +31 (0) 84 22 09880   :
: Koemaad 26 : m: [hidden email]     :
: 8431 TM Oosterwolde : w: www.pero-ict.nl       :
+-------------------------------+--------------------------+
: OpenPGP 0x0F655F0D (random.sks.keyserver.penguin.de)   :
+----------------------------------------------------------+





-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFCw+Wv9bwznA9lXw0RAkkuAJwPEFx2fA1J16OYQQI//9ONgD+95ACfcEPR
EQrFyOkRT01rRHd24xt70O8=
=qmdI
-----END PGP SIGNATURE-----
Reply | Threaded
Open this post in threaded view
|

Re: SQLite.NET in-memory database creation

Peter Berkenbosch
In reply to this post by Brad-19
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brass Tilde wrote:

>>>Stupid question, but how can i create an in-memory database with
>>>SQLite.NET provider? When i use Data Source=:memory: i get an exception
>>>that the specified file isn't found.
>>
>>If you are using the ADOSQLiteDotNet provider from SourceForge, add "New=True"
>>to your connection string.
>
>
> To clarify, my connection strings for in-memory databases look like this:
>
> "Data Source=:memory:;Version=3;New=True"
>
>

mmm. looks like it only fails with version=2; thx for the input.

- --
+-------------------------------+--------------------------+
: Peter Berkenbosch :   :
: : t: +31 (0) 64 84 61653   :
: PeRo ICT Solutions : f: +31 (0) 84 22 09880   :
: Koemaad 26 : m: [hidden email]     :
: 8431 TM Oosterwolde : w: www.pero-ict.nl       :
+-------------------------------+--------------------------+
: OpenPGP 0x0F655F0D (random.sks.keyserver.penguin.de)   :
+----------------------------------------------------------+





-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFCw+oO9bwznA9lXw0RAmjnAJ9PuIJuAPT89EfIX2j0h82QIYEHuwCglCaa
kPj1DUjfGPJr9+mfiGAvyu8=
=8mhF
-----END PGP SIGNATURE-----
Reply | Threaded
Open this post in threaded view
|

Re: Insert all rows from old table into new table but in sorted order

D. Richard Hipp
In reply to this post by ajay-7
On Thu, 2005-06-30 at 16:35 +0530, Ajay wrote:
> So I used query
> Insert into NEWTABLE select * from OLDTABLE order by no desc
> But it is not giving me sorted output as new table?
>
> Can you tell me where I am wrong ???
>

The ORDER BY clause on a SELECT used to insert into a table
has been honored since SQLite version 2.7.2 (Sep 2002).  You
must be using a really old version of SQLite.

In SQLite, the INTEGER PRIMARY KEY (a.k.a. ROWID) acts as a
cluster index.  Any query on a table that does not use an
index or an ORDER BY clause will output rows in ROWID order.
This is not a guarantee, but it is how things work now.  What
is guaranteed is that rows are stored in a table in order of
ascending ROWID.

When new rows are added to a table, unless the ROWID overflows
or is specified, each new row is added at the end.  So if you
do

    CREATE TABLE newtable AS SELECT * FROM oldtable ORDER BY x;
    SELECT * FROM newtable;

The results will come out sorted by X.  Again - this is not
guaranteed but it is how things are currently implemented.
It might change tomorrow and I would not consider that an
incompatible change.  If you want things in a specific order,
then use an ORDER BY clause.  But the behavior you seek is
the current behavior.

Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

Brad DerManouelian
In reply to this post by ajay-7
The solution is to always specify an order when you want to return data
in a particular order. SQL standard does not specify that rows come back
in a particular order unless you specify. Order is never assumed as to
enhance speed - especially for functions where order is irrelevant like
totaling columns.

If you're familiar with how hashes are stored in C++ or Perl (and others
I'm not familiar with), it's the same situation. If you don't pass it
through a sort function, the interpreter decides how to return the
values in the most efficient way which may not be the way they were
originally inserted.

_brad


-----Original Message-----
From: Ajay [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 8:21 AM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


Yaa that's what I wanted to do , So what do you think what could be the
solution for this ?


-----Original Message-----
From: Steve O'Hara [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 5:28 PM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


I might be wrong, but if you don't specify a sort column, you will get
the
rows out in PRIMARY KEY order, irrespective of how you loaded the data.
Therefore, you will need to do something a little more interesting with
your
loading statement to perhaps exclude the primary key and let the insert
re-generate them.

Just a thought.

Steve

-----Original Message-----
From: sqlite-users-return-6291-sohara=[hidden email]
[mailto:sqlite-users-return-6291-sohara=[hidden email]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



>I can insert all rows of existing table into new table having same
columns

>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE',
the
results are returned in an undefined order - not necessarily in the
order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/







Reply | Threaded
Open this post in threaded view
|

Re: Insert all rows from old table into new table but in sorted order

Puneet Kishor
In reply to this post by ajay-7

On Jun 30, 2005, at 7:21 AM, Ajay wrote:

>
> Yaa that's what I wanted to do , So what do you think what could be the
> solution for this ?

well, as others have suggested, there is no solution for it. Or, at  
least no solution that you should bother with. The purpose of the  
database is not to store the data in some particular view (order,  
collation, grouping, etc.) that you might want to view it later in.  
That is the reason the db provides methods to generate the views the  
way you want them. The only imposition is that the db might sort them  
internally by the PK, but that is also irrelevant -- the PK may not  
always be numerical (as in the case of a GUID), hence, you could get  
any old thing.

In short, don't bother trying to insert the data in a particular order,  
because that is not what the db is designed for. Once you have the data  
in the db, then use the power of the db to morph the data into whatever  
views your heart desires.

>
>
> -----Original Message-----
> From: Steve O'Hara [mailto:[hidden email]]
> Sent: Thursday, June 30, 2005 5:28 PM
> To: [hidden email]
> Subject: RE: [sqlite] Insert all rows from old table into new table  
> but in
> sorted order
>
>
> I might be wrong, but if you don't specify a sort column, you will get  
> the
> rows out in PRIMARY KEY order, irrespective of how you loaded the data.
> Therefore, you will need to do something a little more interesting  
> with your
> loading statement to perhaps exclude the primary key and let the insert
> re-generate them.
>
> Just a thought.
>
> Steve
>
> -----Original Message-----
> From:  
> sqlite-users-return-6291-sohara=[hidden email]
> [mailto:sqlite-users-return-6291-sohara=pivotal-
> [hidden email]
> rg]On Behalf Of Paul Smith
> Sent: 30 June 2005 12:23
> To: [hidden email]
> Subject: Re: [sqlite] Insert all rows from old table into new table but
> in sorted order
>
>
>
>> I can insert all rows of existing table into new table having same  
>> columns
>> using query :
>>
>> Insert into NEWTABLE select * from OLDTABLE
>>
>> But I want all rows of NEWTABLE sorted by field No,
>>
>> So I used query
>>
>> Insert into NEWTABLE select * from OLDTABLE order by no desc
>>
>> But it is not giving me sorted output as new table?
>>
>> Can you tell me where I am wrong ???
>
> You can't do that.
>
> The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in  
> the
> "right order" , but then, when you do an unordered query on  
> 'NEWTABLE', the
> results are returned in an undefined order - not necessarily in the  
> order
> they were inserted into the table
>
> You should do the sorting when you read 'NEWTABLE'
>
> So, instead of
>
> Insert into NEWTABLE select * from OLDTABLE order by no desc
> select * from NEWTABLE
>
>
> do
>
> Insert into NEWTABLE select * from OLDTABLE
> select * from NEWTABLE order by no desc
>
>
>
>
> Paul                            VPOP3 - Internet Email Server/Gateway
> [hidden email]                      http://www.pscs.co.uk/
>
>
>
>
>
>
>
>
--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

ajay-7
In reply to this post by Brad DerManouelian


Seems to be top of my head, Is there any simple and sweet solution ?



-----Original Message-----
From: Brad DerManouelian [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 6:36 PM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but in
sorted order

The solution is to always specify an order when you want to return data
in a particular order. SQL standard does not specify that rows come back
in a particular order unless you specify. Order is never assumed as to
enhance speed - especially for functions where order is irrelevant like
totaling columns.

If you're familiar with how hashes are stored in C++ or Perl (and others
I'm not familiar with), it's the same situation. If you don't pass it
through a sort function, the interpreter decides how to return the
values in the most efficient way which may not be the way they were
originally inserted.

_brad


-----Original Message-----
From: Ajay [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 8:21 AM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


Yaa that's what I wanted to do , So what do you think what could be the
solution for this ?


-----Original Message-----
From: Steve O'Hara [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 5:28 PM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


I might be wrong, but if you don't specify a sort column, you will get
the
rows out in PRIMARY KEY order, irrespective of how you loaded the data.
Therefore, you will need to do something a little more interesting with
your
loading statement to perhaps exclude the primary key and let the insert
re-generate them.

Just a thought.

Steve

-----Original Message-----
From: sqlite-users-return-6291-sohara=[hidden email]
[mailto:sqlite-users-return-6291-sohara=[hidden email]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



>I can insert all rows of existing table into new table having same
columns

>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE',
the
results are returned in an undefined order - not necessarily in the
order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/








Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

Brad DerManouelian
In reply to this post by ajay-7
My apologies for being long-winded. Basically the answer is to not
insert in a particular order and do your order by when you recall the
data from NEWTABLE.

Insert the data:
Insert into NEWTABLE select * from OLDTABLE

Then to get the data back in the order you want:
select * from NEWTABLE order by no desc

_brad

-----Original Message-----
From: Ajay [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 9:38 AM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order



Seems to be top of my head, Is there any simple and sweet solution ?



-----Original Message-----
From: Brad DerManouelian [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 6:36 PM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order

The solution is to always specify an order when you want to return data
in a particular order. SQL standard does not specify that rows come back
in a particular order unless you specify. Order is never assumed as to
enhance speed - especially for functions where order is irrelevant like
totaling columns.

If you're familiar with how hashes are stored in C++ or Perl (and others
I'm not familiar with), it's the same situation. If you don't pass it
through a sort function, the interpreter decides how to return the
values in the most efficient way which may not be the way they were
originally inserted.

_brad


-----Original Message-----
From: Ajay [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 8:21 AM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


Yaa that's what I wanted to do , So what do you think what could be the
solution for this ?


-----Original Message-----
From: Steve O'Hara [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 5:28 PM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


I might be wrong, but if you don't specify a sort column, you will get
the
rows out in PRIMARY KEY order, irrespective of how you loaded the data.
Therefore, you will need to do something a little more interesting with
your
loading statement to perhaps exclude the primary key and let the insert
re-generate them.

Just a thought.

Steve

-----Original Message-----
From: sqlite-users-return-6291-sohara=[hidden email]
[mailto:sqlite-users-return-6291-sohara=[hidden email]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



>I can insert all rows of existing table into new table having same
columns

>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE',
the
results are returned in an undefined order - not necessarily in the
order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/








Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

ajay-7
Ok, it means that I can't do so !



-----Original Message-----
From: Brad DerManouelian [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 7:33 PM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but in
sorted order

My apologies for being long-winded. Basically the answer is to not
insert in a particular order and do your order by when you recall the
data from NEWTABLE.

Insert the data:
Insert into NEWTABLE select * from OLDTABLE

Then to get the data back in the order you want:
select * from NEWTABLE order by no desc

_brad

-----Original Message-----
From: Ajay [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 9:38 AM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order



Seems to be top of my head, Is there any simple and sweet solution ?



-----Original Message-----
From: Brad DerManouelian [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 6:36 PM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order

The solution is to always specify an order when you want to return data
in a particular order. SQL standard does not specify that rows come back
in a particular order unless you specify. Order is never assumed as to
enhance speed - especially for functions where order is irrelevant like
totaling columns.

If you're familiar with how hashes are stored in C++ or Perl (and others
I'm not familiar with), it's the same situation. If you don't pass it
through a sort function, the interpreter decides how to return the
values in the most efficient way which may not be the way they were
originally inserted.

_brad


-----Original Message-----
From: Ajay [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 8:21 AM
To: [hidden email]; [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


Yaa that's what I wanted to do , So what do you think what could be the
solution for this ?


-----Original Message-----
From: Steve O'Hara [mailto:[hidden email]]
Sent: Thursday, June 30, 2005 5:28 PM
To: [hidden email]
Subject: RE: [sqlite] Insert all rows from old table into new table but
in sorted order


I might be wrong, but if you don't specify a sort column, you will get
the
rows out in PRIMARY KEY order, irrespective of how you loaded the data.
Therefore, you will need to do something a little more interesting with
your
loading statement to perhaps exclude the primary key and let the insert
re-generate them.

Just a thought.

Steve

-----Original Message-----
From: sqlite-users-return-6291-sohara=[hidden email]
[mailto:sqlite-users-return-6291-sohara=[hidden email]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



>I can insert all rows of existing table into new table having same
columns

>using query :
>
>Insert into NEWTABLE select * from OLDTABLE
>
>But I want all rows of NEWTABLE sorted by field No,
>
>So I used query
>
>Insert into NEWTABLE select * from OLDTABLE order by no desc
>
>But it is not giving me sorted output as new table?
>
>Can you tell me where I am wrong ???

You can't do that.

The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the
"right order" , but then, when you do an unordered query on 'NEWTABLE',
the
results are returned in an undefined order - not necessarily in the
order
they were inserted into the table

You should do the sorting when you read 'NEWTABLE'

So, instead of

Insert into NEWTABLE select * from OLDTABLE order by no desc
select * from NEWTABLE


do

Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc




Paul                            VPOP3 - Internet Email Server/Gateway
[hidden email]                      http://www.pscs.co.uk/









Reply | Threaded
Open this post in threaded view
|

RE: Insert all rows from old table into new table but in sorted order

Steve O'Hara
In reply to this post by Puneet Kishor

Some databases do actually allow you to maintain an insertion order.

They do this for performance reasons so that the high cost of sorting is
avoided - we have a few newspaper databases (>30 million full text stories)
that have their primary key defined as the inverse story insertion date -
this means that when a journalist searches for a story, they always get the
results in 'latest first' order, which is nearly always what they want.

Typically, a journalist will run searches that return 10's of thousands of
results, multiply that by the number of users within the paper (could be
hundreds worldwide) and you can begin to see why this 'pre-sorting' of
results is very useful.

It's a little acedemic to say that a database should not store its data in a
particular way, in a similar way to saying that all data MUST be normalised.
In the real world, where performance is maybe more important than storage
space, imposing a scheme on the data can be very important.

Steve


-----Original Message-----
From: sqlite-users-return-6304-sohara=[hidden email]
[mailto:sqlite-users-return-6304-sohara=[hidden email]
rg]On Behalf Of Puneet Kishor
Sent: 30 June 2005 14:09
To: [hidden email]
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order



On Jun 30, 2005, at 7:21 AM, Ajay wrote:

>
> Yaa that's what I wanted to do , So what do you think what could be the
> solution for this ?

well, as others have suggested, there is no solution for it. Or, at
least no solution that you should bother with. The purpose of the
database is not to store the data in some particular view (order,
collation, grouping, etc.) that you might want to view it later in.
That is the reason the db provides methods to generate the views the
way you want them. The only imposition is that the db might sort them
internally by the PK, but that is also irrelevant -- the PK may not
always be numerical (as in the case of a GUID), hence, you could get
any old thing.

In short, don't bother trying to insert the data in a particular order,
because that is not what the db is designed for. Once you have the data
in the db, then use the power of the db to morph the data into whatever
views your heart desires.

>
>
> -----Original Message-----
> From: Steve O'Hara [mailto:[hidden email]]
> Sent: Thursday, June 30, 2005 5:28 PM
> To: [hidden email]
> Subject: RE: [sqlite] Insert all rows from old table into new table
> but in
> sorted order
>
>
> I might be wrong, but if you don't specify a sort column, you will get
> the
> rows out in PRIMARY KEY order, irrespective of how you loaded the data.
> Therefore, you will need to do something a little more interesting
> with your
> loading statement to perhaps exclude the primary key and let the insert
> re-generate them.
>
> Just a thought.
>
> Steve
>
> -----Original Message-----
> From:
> sqlite-users-return-6291-sohara=[hidden email]
> [mailto:sqlite-users-return-6291-sohara=pivotal-
> [hidden email]
> rg]On Behalf Of Paul Smith
> Sent: 30 June 2005 12:23
> To: [hidden email]
> Subject: Re: [sqlite] Insert all rows from old table into new table but
> in sorted order
>
>
>
>> I can insert all rows of existing table into new table having same
>> columns
>> using query :
>>
>> Insert into NEWTABLE select * from OLDTABLE
>>
>> But I want all rows of NEWTABLE sorted by field No,
>>
>> So I used query
>>
>> Insert into NEWTABLE select * from OLDTABLE order by no desc
>>
>> But it is not giving me sorted output as new table?
>>
>> Can you tell me where I am wrong ???
>
> You can't do that.
>
> The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in
> the
> "right order" , but then, when you do an unordered query on
> 'NEWTABLE', the
> results are returned in an undefined order - not necessarily in the
> order
> they were inserted into the table
>
> You should do the sorting when you read 'NEWTABLE'
>
> So, instead of
>
> Insert into NEWTABLE select * from OLDTABLE order by no desc
> select * from NEWTABLE
>
>
> do
>
> Insert into NEWTABLE select * from OLDTABLE
> select * from NEWTABLE order by no desc
>
>
>
>
> Paul                            VPOP3 - Internet Email Server/Gateway
> [hidden email]                      http://www.pscs.co.uk/
>
>
>
>
>
>
>
>
--
Puneet Kishor




Reply | Threaded
Open this post in threaded view
|

Re: Insert all rows from old table into new table but in sorted order

Puneet Kishor
>


On Jun 30, 2005, at 9:47 AM, Steve O'Hara wrote:

>
> Some databases do actually allow you to maintain an insertion order.
>
> They do this for performance reasons so that the high cost of sorting  
> is
> avoided - we have a few newspaper databases (>30 million full text  
> stories)
> that have their primary key defined as the inverse story insertion  
> date -
> this means that when a journalist searches for a story, they always  
> get the
> results in 'latest first' order, which is nearly always what they want.
>
> Typically, a journalist will run searches that return 10's of  
> thousands of
> results, multiply that by the number of users within the paper (could  
> be
> hundreds worldwide) and you can begin to see why this 'pre-sorting' of
> results is very useful.
>
> It's a little acedemic to say that a database should not store its  
> data in a
> particular way, in a similar way to saying that all data MUST be  
> normalised.
> In the real world, where performance is maybe more important than  
> storage
> space, imposing a scheme on the data can be very important.
>


well, just for the sake of academics, I quote myself (original message  
below) --

> The only imposition is that the db might sort them
> internally by the PK, but that is also irrelevant -- the PK may not
> always be numerical (as in the case of a GUID), hence, you could get
> any old thing.

So, you are saying the same thing as I said. The db didn't really sort  
it any particular way. _You_ made the inverse insertion date to be the  
PK, and the db naturally returned it so. The PK could well have been  
something that might not sortable in the human-comprehensible way, such  
as a GUID.

A reference was made to Perl hashes coming out randomly. Actually,  
internally (from what I understand), the hashes are also stored as  
arrays, and they do come out sorted a certain way. It is just that  
_that_ way might not be _the_ way we expect or can understand. Hence,  
the need, and therefore, the provision, for externally available  
SORTing methods...


> -----Original Message-----
> From:  
> sqlite-users-return-6304-sohara=[hidden email]
> [mailto:sqlite-users-return-6304-sohara=pivotal-
> [hidden email]
> rg]On Behalf Of Puneet Kishor
> Sent: 30 June 2005 14:09
> To: [hidden email]
> Subject: Re: [sqlite] Insert all rows from old table into new table but
> in sorted order
>
>
>
> On Jun 30, 2005, at 7:21 AM, Ajay wrote:
>
>>
>> Yaa that's what I wanted to do , So what do you think what could be  
>> the
>> solution for this ?
>
> well, as others have suggested, there is no solution for it. Or, at
> least no solution that you should bother with. The purpose of the
> database is not to store the data in some particular view (order,
> collation, grouping, etc.) that you might want to view it later in.
> That is the reason the db provides methods to generate the views the
> way you want them. The only imposition is that the db might sort them
> internally by the PK, but that is also irrelevant -- the PK may not
> always be numerical (as in the case of a GUID), hence, you could get
> any old thing.
>
> In short, don't bother trying to insert the data in a particular order,
> because that is not what the db is designed for. Once you have the data
> in the db, then use the power of the db to morph the data into whatever
> views your heart desires.
>
>>
>>
>> -----Original Message-----
>> From: Steve O'Hara [mailto:[hidden email]]
>> Sent: Thursday, June 30, 2005 5:28 PM
>> To: [hidden email]
>> Subject: RE: [sqlite] Insert all rows from old table into new table
>> but in
>> sorted order
>>
>>
>> I might be wrong, but if you don't specify a sort column, you will get
>> the
>> rows out in PRIMARY KEY order, irrespective of how you loaded the  
>> data.
>> Therefore, you will need to do something a little more interesting
>> with your
>> loading statement to perhaps exclude the primary key and let the  
>> insert
>> re-generate them.
>>
>> Just a thought.
>>
>> Steve
>>
>> -----Original Message-----
>> From:
>> sqlite-users-return-6291-sohara=[hidden email]
>> [mailto:sqlite-users-return-6291-sohara=pivotal-
>> [hidden email]
>> rg]On Behalf Of Paul Smith
>> Sent: 30 June 2005 12:23
>> To: [hidden email]
>> Subject: Re: [sqlite] Insert all rows from old table into new table  
>> but
>> in sorted order
>>
>>
>>
>>> I can insert all rows of existing table into new table having same
>>> columns
>>> using query :
>>>
>>> Insert into NEWTABLE select * from OLDTABLE
>>>
>>> But I want all rows of NEWTABLE sorted by field No,
>>>
>>> So I used query
>>>
>>> Insert into NEWTABLE select * from OLDTABLE order by no desc
>>>
>>> But it is not giving me sorted output as new table?
>>>
>>> Can you tell me where I am wrong ???
>>
>> You can't do that.
>>
>> The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in
>> the
>> "right order" , but then, when you do an unordered query on
>> 'NEWTABLE', the
>> results are returned in an undefined order - not necessarily in the
>> order
>> they were inserted into the table
>>
>> You should do the sorting when you read 'NEWTABLE'
>>
>> So, instead of
>>
>> Insert into NEWTABLE select * from OLDTABLE order by no desc
>> select * from NEWTABLE
>>
>>
>> do
>>
>> Insert into NEWTABLE select * from OLDTABLE
>> select * from NEWTABLE order by no desc
>>
>>
>>
>>
>> Paul                            VPOP3 - Internet Email Server/Gateway
>> [hidden email]                      http://www.pscs.co.uk/
>>
>>
>>
>>
>>
>>
>>
>>
> --
> Puneet Kishor
>
>
>
>
>
--
Puneet Kishor

12