SEO

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

On Apr 28, 11:14 pm, laredotornado <laredotorn...@zipmail.com> 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