On 26 Nov, 10:04, "Bob Bedford" <b...@bedford.com> wrote:
> 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.

SELECT
*
FROM simple_table
ORDER BY savedate

> 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/

> 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)

The idea that comes to mind is to create a new table like the old one,
but add your AUTO_INCREMENT column. Then write a script or something
to:

SELECT * FROM historytable ORDER BY date DESC

INSERT INTO newtable VALUES (NULL, $user, $date)

Shutdown MySQL, rename your old tables to something else.. rename the
new tables to historytable. Start MySQL. You should now have an
auto_increment column that's up to date, and the entries ordered.