Hi,

I'm using MySQL 5.0. I have a couple of columns in my table T ...

ID INTEGER UNSIGNED AUTO_INCREMENT,
ORDER_ID INTEGER UNSIGNED

The "ID" column is the primary key. What I want to do is set the
ORDER_ID column to be the MAX of the ID's minus the ID value. So for
example if I have the following ID values, I'd want ORDER_ID to have
these values

ID ORDER_ID
------------------------------
1 4
2 3
3 2
4 1

You may assume that no rows have been deleted from the table.

Thanks, - Dave

On Wed, 02 Jan 2008 19:53:32 +0100, laredotornado@zipmail.com
<laredotornado@zipmail.com> wrote:

> Hi,
>
> I'm using MySQL 5.0. I have a couple of columns in my table T ...
>
> ID INTEGER UNSIGNED AUTO_INCREMENT,
> ORDER_ID INTEGER UNSIGNED
>
> The "ID" column is the primary key. What I want to do is set the
> ORDER_ID column to be the MAX of the ID's minus the ID value.

Why would you need this? If it's for ordering, why not simply use a order
by ID DESC?
--
Rik Wasmus

laredotornado@zipmail.com wrote:
> Hi,
>
> I'm using MySQL 5.0. I have a couple of columns in my table T ...
>
> ID INTEGER UNSIGNED AUTO_INCREMENT,
> ORDER_ID INTEGER UNSIGNED
>
> The "ID" column is the primary key. What I want to do is set the
> ORDER_ID column to be the MAX of the ID's minus the ID value. So for
> example if I have the following ID values, I'd want ORDER_ID to have
> these values
>
> ID ORDER_ID
> ------------------------------
> 1 4
> 2 3
> 3 2
> 4 1
>
> You may assume that no rows have been deleted from the table.
>
> Thanks, - Dave


you can do this with a trigger. read up on that on mysql's site. it's
pretty straight forward.

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> schreef in bericht
newsp.t4bgtbjy5bnjuv@metallium.lan...
> On Wed, 02 Jan 2008 19:53:32 +0100, laredotornado@zipmail.com
> <laredotornado@zipmail.com> wrote:
>
>> Hi,
>>
>> I'm using MySQL 5.0. I have a couple of columns in my table T ...
>>
>> ID INTEGER UNSIGNED AUTO_INCREMENT,
>> ORDER_ID INTEGER UNSIGNED
>>
>> The "ID" column is the primary key. What I want to do is set the
>> ORDER_ID column to be the MAX of the ID's minus the ID value.
>
> Why would you need this? If it's for ordering, why not simply use a order
> by ID DESC?
> --
> Rik Wasmus

indeed, you can do this ID DESC,
and would the option of the OP not be verry slow if one has a lot of
records?

On Wed, 2 Jan 2008 10:53:32 -0800 (PST),
"laredotornado@zipmail.com" <laredotornado@zipmail.com>
wrote:

>Hi,
>
>I'm using MySQL 5.0. I have a couple of columns in my table T ...
>
>ID INTEGER UNSIGNED AUTO_INCREMENT,
>ORDER_ID INTEGER UNSIGNED
>
>The "ID" column is the primary key. What I want to do is set the
>ORDER_ID column to be the MAX of the ID's minus the ID value. So for
>example if I have the following ID values, I'd want ORDER_ID to have
>these values
>
>ID ORDER_ID
>------------------------------
>1 4
>2 3
>3 2
>4 1

Your example contradicts your description.
max(ID) = 4, so ORDER_ID column would be:

ID ORDER_ID
------------------------------
1 3
2 2
3 1
4 0

As otheres already explained, if this is a fixed
relationship, you don't need the ORDER_ID.
Just do SELECT ..... ORDER BY ID DESC.
--
( Kees
)
c[_] Virtue is its own punishment. (#240)

On Jan 2, 4:23pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Wed, 2 Jan 2008 10:53:32 -0800 (PST),
> "laredotorn...@zipmail.com" <laredotorn...@zipmail.com>
> wrote:
>
>
>
>
>
> >Hi,
>
> >I'm using MySQL 5.0. I have a couple of columns in my table T ...
>
> >ID INTEGER UNSIGNED AUTO_INCREMENT,
> >ORDER_ID INTEGER UNSIGNED
>
> >The "ID" column is the primary key. What I want to do is set the
> >ORDER_ID column to be the MAX of the ID's minus the ID value. So for
> >example if I have the following ID values, I'd want ORDER_ID to have
> >these values
>
> >ID ORDER_ID
> >------------------------------
> >1 4
> >2 3
> >3 2
> >4 1
>
> Your example contradicts your description.
> max(ID) = 4, so ORDER_ID column would be:
>
> ID ORDER_ID
> ------------------------------
> 1 3
> 2 2
> 3 1
> 4 0
>
> As otheres already explained, if this is a fixed
> relationship, you don't need the ORDER_ID.
> Just do SELECT ..... ORDER BY ID DESC.
> --
> ( Kees
> )
> c[_] Virtue is its own punishment. (#240)- Hide quoted text -
>
> - Show quoted text -

The reason I don't want to use "ID DESC" is that I have an option in
the admin to move items up or down, thus changing their order id. I
don't want to have to change the value of the primary key (ID) as
other things may be depending on the primary key. I want the UPDATE
statement b/c I need to seed the order_id with some value and then
from there, it might change.

Thanks for any more info, - Dave

laredotornado@zipmail.com wrote:
> The reason I don't want to use "ID DESC" is that I have an option in
> the admin to move items up or down, thus changing their order id. I
> don't want to have to change the value of the primary key (ID) as
> other things may be depending on the primary key. I want the UPDATE
> statement b/c I need to seed the order_id with some value and then
> from there, it might change.
>
> Thanks for any more info, - Dave

So seed the order_id with the current primary key and then used order_id
DESC!