Hello,

I've an old very simple table that was only used to save datas. Now this
table needs a primary key. In order to add one, I've to sort the results by
savedate.

How to do so ? Actually when I do a select the order is taken on the first
field that I absolutely don't want to use for sorting.

Thanks for helping.

On 26 Nov, 10:42, Willem Bogaerts
<w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> > I've an old very simple table that was only used to save datas. Now this
> > table needs a primary key. In order to add one, I've to sort the results by
> > savedate.
>
> Why? Is your date field the field that you want to "promote" to primary
> key? or do you want to add an autonumber column?
>
> Primary keys should have nothing to do with the values of the records
> and should not even be abused for sorting. The primary key exists to
> give your record a unique identity that exceeds its lifespan (that is
> why a primary key value is not reclaimed when you delete the record).
>
> If you want to change the default order of the table, you can change it:
>
> ALTER TABLE <table name> ORDER BY <the date field>;
>
> Best regards,
> --
> Willem Bogaerts
>
> Application smith
> Kratz B.V.http://www.kratz.nl/

Not quite default order:
"ORDER BY enables you to create the new table with the rows in a
specific order. Note that the table does not remain in this order
after inserts and deletes."

"Willem Bogaerts" <w.bogaerts@kratz.maardanzonderditstuk.nl> a écrit dans le
message de news: 474aa330$0$240$e4fe514c@news.xs4all.nl...
>> I've an old very simple table that was only used to save datas. Now this
>> table needs a primary key. In order to add one, I've to sort the results
>> by
>> savedate.
>
> Why? Is your date field the field that you want to "promote" to primary
> key? or do you want to add an autonumber column?
>
> Primary keys should have nothing to do with the values of the records
> and should not even be abused for sorting. The primary key exists to
> give your record a unique identity that exceeds its lifespan (that is
> why a primary key value is not reclaimed when you delete the record).
>
> If you want to change the default order of the table, you can change it:
>
> ALTER TABLE <table name> ORDER BY <the date field>;
>
> Best regards,
> --
> Willem Bogaerts
Hello Willem, thanks for replying.

I want to add an autonumber column but set the actual record autonumber
based on date field order.
I mean actually I've this when I do a select * from historytable:
user date
1 2006-10-10
2 2006-10-15
3 2006-09-01
3 2006-09-10

if I do a select, the order is based on the user number. So adding an
auto_increment field to this table will certainly add like show in the
example above. Instead I'd like the sorting by date to add the new
autoincrement values:
AutoIncrement user date
1 3 2006-09-01
2 3 2006-09-10
3 1 2006-10-10
4 2 2006-10-15

So I've to sort by date to set the auto_increment value.
Like: alter table historytable add auto_increment on (select * from
historytable order by date asc)
How to do so ?
Bob

Bob Bedford wrote:
> "Willem Bogaerts" <w.bogaerts@kratz.maardanzonderditstuk.nl> a écrit dans le
> message de news: 474aa330$0$240$e4fe514c@news.xs4all.nl...
>>> I've an old very simple table that was only used to save datas. Now this
>>> table needs a primary key. In order to add one, I've to sort the results
>>> by
>>> savedate.
>> Why? Is your date field the field that you want to "promote" to primary
>> key? or do you want to add an autonumber column?
>>
>> Primary keys should have nothing to do with the values of the records
>> and should not even be abused for sorting. The primary key exists to
>> give your record a unique identity that exceeds its lifespan (that is
>> why a primary key value is not reclaimed when you delete the record).
>>
>> If you want to change the default order of the table, you can change it:
>>
>> ALTER TABLE <table name> ORDER BY <the date field>;
>>
>> Best regards,
>> --
>> Willem Bogaerts
> Hello Willem, thanks for replying.
>
> I want to add an autonumber column but set the actual record autonumber
> based on date field order.
> I mean actually I've this when I do a select * from historytable:
> user date
> 1 2006-10-10
> 2 2006-10-15
> 3 2006-09-01
> 3 2006-09-10
>
> if I do a select, the order is based on the user number. So adding an
> auto_increment field to this table will certainly add like show in the
> example above. Instead I'd like the sorting by date to add the new
> autoincrement values:
> AutoIncrement user date
> 1 3 2006-09-01
> 2 3 2006-09-10
> 3 1 2006-10-10
> 4 2 2006-10-15
>
> So I've to sort by date to set the auto_increment value.
> Like: alter table historytable add auto_increment on (select * from
> historytable order by date asc)
> How to do so ?
> Bob
>
>
>

No, if you just do a SELECT, the returned order is not defined. It's
just how the rows happen to occur in the database. If you want a
specific order you must use the ORDER BY clause.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mon, 26 Nov 2007 13:45:45 +0100, Bob Bedford wrote:

> "Willem Bogaerts" <w.bogaerts@kratz.maardanzonderditstuk.nl> a écrit
> dans le message de news: 474aa330$0$240$e4fe514c@news.xs4all.nl...
>
>>> I've an old very simple table that was only used to save datas. Now
>>> this table needs a primary key. In order to add one, I've to sort
>>> the results by savedate.
>>
>> Why? Is your date field the field that you want to "promote" to
>> primary key? or do you want to add an autonumber column?
>>
>> Primary keys should have nothing to do with the values of the records
>> and should not even be abused for sorting. The primary key exists to
>> give your record a unique identity that exceeds its lifespan (that is
>> why a primary key value is not reclaimed when you delete the record).
>>
>> If you want to change the default order of the table, you can change
>> it:
>>
>> ALTER TABLE <table name> ORDER BY <the date field>;
>>
>> Best regards,
>> --
>> Willem Bogaerts
> Hello Willem, thanks for replying.
>
> I want to add an autonumber column but set the actual record autonumber
> based on date field order.

Again, why? Specifically, what do you feel you would gain by doing this?

--
Crowds want to beat, journalists deserve to be beaten. Where lies
the problem?
-- Lars Syrstad

> No, if you just do a SELECT, the returned order is not defined. It's just
> how the rows happen to occur in the database. If you want a specific
> order you must use the ORDER BY clause.

I know but my original question is:

how to add an autoincrement value to a table assigning the new autoincrement
values in date order on existing records.

>> I want to add an autonumber column but set the actual record autonumber
>> based on date field order.
>
> Again, why? Specifically, what do you feel you would gain by doing this?
>
The datas have been created using the date of the day (date field). Since
this is the natural order they have been created and since we will use the
new AutoIncrement field for retrieving datas (where autonumber > ...) we
will it this way.

Bob

Bob Bedford wrote:
>> No, if you just do a SELECT, the returned order is not defined. It's just
>> how the rows happen to occur in the database. If you want a specific
>> order you must use the ORDER BY clause.
>
> I know but my original question is:
>
> how to add an autoincrement value to a table assigning the new autoincrement
> values in date order on existing records.
>
>
>

And the point is - you can't guarantee the order unless you use ORDER BY.

But your entire premise is flawed. The autoincremement column is to
uniquely identify the row. It show have no dependence on any data.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Bob Bedford wrote:
>>> I want to add an autonumber column but set the actual record autonumber
>>> based on date field order.
>> Again, why? Specifically, what do you feel you would gain by doing this?
>>
> The datas have been created using the date of the day (date field). Since
> this is the natural order they have been created and since we will use the
> new AutoIncrement field for retrieving datas (where autonumber > ...) we
> will it this way.
>
> Bob
>
>
>

Bob.

Your assumption that that will work is incorrect. If you need to
retrieve data based on a date, then compare on the date field.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mon, 26 Nov 2007 15:47:26 +0100, Bob Bedford wrote:
>>> I want to add an autonumber column but set the actual record autonumber
>>> based on date field order.
>>
>> Again, why? Specifically, what do you feel you would gain by doing this?
>>
> The datas have been created using the date of the day (date field). Since
> this is the natural order they have been created and since we will use the
> new AutoIncrement field for retrieving datas (where autonumber > ...) we
> will it this way.

So you want to autoincrement by date order in order to retrieve in date
order. Why not select and order data by date?

--
67. No matter how many shorts we have in the system, my guards will be
instructed to treat every surveillance camera malfunction as a
full-scale emergency.
--Peter Anspach's list of things to do as an Evil Overlord