Hi all,

I'm trying to add an auto_increment field on a table that doesn't have
primary key.

What I'd like is to have the auto_increment order based on a date field for
every record.

To do so, I've:

1 - changed the position of this field to the first position
2 - added the auto_increment field and set it as primary key
3 - changed the position of the date field back.

I Tought the order of the records was taken based on the field value that's
why I placed on the first place but it didn't change anything.

I tried to create a little program in Delphi but with no success as even
trying to set manually the value, when setting the auto_increment field then
the values are re-affected.

How can I do that ? I absolutely need to sort the values based on the date
for setting the auto_Increment value.

Bob

Bob Bedford schreef:
> Hi all,
>
> I'm trying to add an auto_increment field on a table that doesn't have
> primary key.
>
> What I'd like is to have the auto_increment order based on a date field for
> every record.
>
> To do so, I've:
>
> 1 - changed the position of this field to the first position
> 2 - added the auto_increment field and set it as primary key
> 3 - changed the position of the date field back.
>
> I Tought the order of the records was taken based on the field value that's
> why I placed on the first place but it didn't change anything.
>
> I tried to create a little program in Delphi but with no success as even
> trying to set manually the value, when setting the auto_increment field then
> the values are re-affected.
>
> How can I do that ? I absolutely need to sort the values based on the date
> for setting the auto_Increment value.
>
> Bob
>
>

set @id:=0;
select @id:=@id+1 as id, yourdate from yourfile
order by yourdate;

if you get it, you can change the select to an UPDATE.

--
Luuk

> set @id:=0;
> select @id:=@id+1 as id, yourdate from yourfile
> order by yourdate;
>
> if you get it, you can change the select to an UPDATE.

Hi Luuk,

Thanks for your . The select works fine, thanks. Unfortunately I'm not
enough "MYSQL aware" to know how to change to an update.
Could you please me a little more ?

Thanks for ing.

Bob

Bob Bedford schreef:
>> set @id:=0;
>> select @id:=@id+1 as id, yourdate from yourfile
>> order by yourdate;
>>
>> if you get it, you can change the select to an UPDATE.
>
> Hi Luuk,
>
> Thanks for your . The select works fine, thanks. Unfortunately I'm not
> enough "MYSQL aware" to know how to change to an update.
> Could you please me a little more ?
>
> Thanks for ing.
>
> Bob
>
>

set @id:=0;
UNDATE yourfile set id=@id:=@id+1 ORDER BY yourdate

--
Luuk

> set @id:=0;
> UNDATE yourfile set id=@id:=@id+1 ORDER BY yourdate

You are a king Luuk !!!!

I spent 3 hours without being able to do it and you did it in 2 lines of
Mysql !!!!

Thanks a lot !!!!!!

Bob Bedford schreef:
>> set @id:=0;
>> UNDATE yourfile set id=@id:=@id+1 ORDER BY yourdate
>
> You are a king Luuk !!!!
>
> I spent 3 hours without being able to do it and you did it in 2 lines of
> Mysql !!!!
>
> Thanks a lot !!!!!!
>
>

somone in this group provided the example some days, (or weeks?) ago ;-)


--
Luuk

On Mon, 11 Feb 2008 23:15:10 +0100, Luuk <Luuk@invalid.lan> wrote:

> Bob Bedford schreef:
>>> set @id:=0;
>>> UNDATE yourfile set id=@id:=@id+1 ORDER BY yourdate
>> You are a king Luuk !!!!
>> I spent 3 hours without being able to do it and you did it in 2 lines
>> of Mysql !!!!
>> Thanks a lot !!!!!!
>
> somone in this group provided the example some days, (or weeks?) ago ;-)

You're welcome :P
--
Rik Wasmus