Statement to set order_id of columns?

On Apr 28, 11:14 pm, laredotornado wrote:
> Hi,
>
> I'm running MySQL 5.0 on Fedora Core 6 Linux. Currently I have a
> table with rows and values like
>
> ID ORDER_ID
> -----------------------------
> 1 1
> 2 3
> 3 5
> 8 5
> 9 7
> 11 15
> 12 15
> 30 18
>
> I would like to write some UPDATE statement that sets the order IDs in
> some consecutive fashion and if there are two order ids with the same
> value, the row with the higher key ID would get the higher order id.
> So, if I ran the statement against the above data, I would want
> results like
>
> ID ORDER_ID
> -----------------------------
> 1 1
> 2 2
> 3 3
> 8 4
> 9 5
> 11 6
> 12 7
> 30 8
>
> Any ideas how to do this? Thanks, - Dave

Depending on exactly what you're after, here's one way. This always
orders by id, ignoring order_id though:

Records: 8 Duplicates: 0 Warnings: 0

SELECT t1.*, COUNT(t1.id) rank
FROM ordering t1
LEFT JOIN ordering t2
ON t1.id >= t2.id
GROUP BY t1.id;

laredotornado wrote:
> Hi,
>
> I'm running MySQL 5.0 on Fedora Core 6 Linux. Currently I have a
> table with rows and values like
>
> ID ORDER_ID
> -----------------------------
> 1 1
> 2 3
> 3 5
> 8 5
> 9 7
> 11 15
> 12 15
> 30 18
>
> I would like to write some UPDATE statement that sets the order IDs in
> some consecutive fashion and if there are two order ids with the same
> value, the row with the higher key ID would get the higher order id.
> So, if I ran the statement against the above data, I would want
> results like
>
> ID ORDER_ID
> -----------------------------
> 1 1
> 2 2
> 3 3
> 8 4
> 9 5
> 11 6
> 12 7
> 30 8
>
> Any ideas how to do this? Thanks, - Dave

SET @OI = 0;
UPDATE table
SET order_id = (@OI := @OI + 1)
ORDER BY order_id, id