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:
[EMAIL PROTECTED]
[mailto:sqlite-users-return-6304-sohara=pivotal-
[EMAIL PROTECTED]
rg]On Behalf Of Puneet Kishor
Sent: 30 June 2005 14:09
To: sqlite-users@sqlite.org
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:[EMAIL PROTECTED]
Sent: Thursday, June 30, 2005 5:28 PM
To: sqlite-users@sqlite.org
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:
[EMAIL PROTECTED]
[mailto:sqlite-users-return-6291-sohara=pivotal-
[EMAIL PROTECTED]
rg]On Behalf Of Paul Smith
Sent: 30 June 2005 12:23
To: sqlite-users@sqlite.org
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
[EMAIL PROTECTED] http://www.pscs.co.uk/
--
Puneet Kishor
--
Puneet Kishor
