MySQL - Number assignment according name ordering

Question

My original table

STUDENTS

    id |    Name     |    Class    |  Order Number |    
   32  |    Xavier   | 10A         | 0             |
   10  |    Mary     | 10B         | 0             |
   35  |    Mandy    | 10B         | 0             |
    4  |    Ruby     | 10A         | 0             |
   12  |    Ben      | 10A         | 0             |
   27  |    Ben      | 10B         | 0             |

And a I need the table to be transformed this way:

STUDENTS

    id |    Name     |    Class    |  Order Number |    
   12  |    Ben      | 10A         | 1             |  
    4  |    Ruby     | 10A         | 2             |
   32  |    Xavier   | 10A         | 3             |
   27  |    Ben      | 10B         | 1             | 
   35  |    Mandy    | 10B         | 2             |
   10  |    Mary     | 10B         | 3             |

How can i do that?

Thanks for any help.

What version of mysql are you on? – P.Salmon Nov 22 '20 at 14:29 — P.Salmon, Nov 22 '20 at 14:29

score 0 · Accepted Answer · answered Nov 22 '20 at 14:29

0

You can use ROW_NUMBER() window function along with the following ORDER BY clause

 SELECT id, name, class, 
        ROW_NUMBER() OVER (PARTITION BY class ORDER BY name) AS "Order Number"
   FROM `students`
  ORDER BY class, name

if DB version is 8+

answered Nov 22 '20 at 14:29

Barbaros Özhan

59,113
10
31
55

Thank you, Mr. Barbaros! – user2671169 Nov 22 '20 at 15:15

MySQL - Number assignment according name ordering

1 Answers1