Beginners question

[rien]

Hi all,

This is one of my first experiences with (My)SQL so I hope you can help me. I use MySQL 4.1.20

I have a table called `csel_contact_details` (it is one of the tables from Joomla! CMS). The four important fields for my question are:

`id` [Key: int(11) auto_increment]
`catid` [int(11) not null]
`name` [varchar(100) not null]
`ordering` [int(11) not null]

I want the table ordered by `catid` and `name` so I did a

ALTER TABLE `csel_contact_details`
ORDER BY `catid`, `name`

I want the presentation of the records to have the same ordening but this ordening depends on the field `ordering`.

Because I have inserted several bulks (hundreds) of records, the ordening is disordered. I can manualy correct the presentation ordering but that is a lot of work with hundreds of records so I am looking for an SQL-statement that does functionally the following:

Order the table by `catid`, `name`,
Go to the first row and set the `ordering` field to 1
Than go to the second row en set the `ordering` field to 2
and so on till the and of the table is reached.

So I thought to define a variable &varOrdering and set that variable to 1 and then build a WHILE-loop setting `ordering`=&varOrdering while incrementing the &varOrdering with every loop.

How can I do that in plain SQL?

Thanks,

Rien Kok

[rien]

Hi all,

I think I have the solution.

SET @pres_order=0;
UPDATE `csel_contact_details` 
SET `ordering` = ( SELECT @pres_order := @pres_order +1 )
ORDER BY `catid`,`name`;

Is there another way to do this?