MySQL: how to sort rows by a field and assign another field a counter?

Question

Imagine there's a table with the following contents:

 x      y     z
 aa     5     null
 bb     2     null
 cc     5     null
 dd     1     null

I want to sort the rows by y and assign an auto-incremented field to z, so in this case the end result (the changed table) would be

 x      y     z
 dd     1     1
 bb     2     2
 aa     5     3
 cc     5     4

or

 x      y     z
 aa     5     3
 bb     2     2
 cc     5     4
 dd     1     1

How do I do that?

So to make it clear, I want to change the table, not get that stuff to code.

As requested, http://sqlfiddle.com/#!2/cd610/1

It would be easier for us to help you if you set up a SQL Fiddle — John Conde
– John Conde, Commented Jun 6, 2013 at 17:56
you can fiddle with user variables, but usually this kind of thing is more easily & reliably left to application code. — Wrikken
– Wrikken, Commented Jun 6, 2013 at 18:00
None was correct. The posters deleted them by themself. Do you want a select or an update query? — juergen d
– juergen d, Commented Jun 6, 2013 at 18:07

juergen d · Accepted Answer · 2013-06-06 18:13:49Z

2

update your_table t1
inner join
(
  select id, @rank := @rank + 1 as r
  from your_table, (select @rank := 0) r
  order by y
) t2 on t2.y = t.y
set z = r

SQLFiddle demo

edited Jun 6, 2013 at 18:13

answered Jun 6, 2013 at 17:59

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-06-06 18:13:31Z

This is what I might call the "painful" solution. It uses a subquery to calculate the rank for each row:

update t
    set z = (select count(*) as cnt
             from (select t.* from t) t2
              where t2.y < t.y or t2.y = t.y and t2.x <= t.x
            )

Note that MySQL generates an error when you use the target table in a subquery in update and delete. There is a way around this. Just embed the table in a subquery.

You can also do this using a variable with a join:

update t join
       (select t.*, (@rank := @rank + 1) as seqnum
        from t cross join (select @rank := 0) const
        order by y
       ) t2
       on t.x = t2.x
set t.z = t2.seqnum;

The subquery calculates the sequence number with the order by. The outer update then assigns the value to the appropriate row. This assumes that x uniquely identifies each row.

Collectives™ on Stack Overflow

MySQL: how to sort rows by a field and assign another field a counter?

2 Answers 2

SQLFiddle demo

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related