Update and increment column by sorting (UPDATE and ORDER BY)

Question

Update and increment column by sorting (UPDATE and ORDER BY)

Navigation

#1 by (1 votes)

3

I have the following table

ID |    NOME     |  ANO  | REGISTO
----------------------------------
0       A          2015      4
1       B          2017      6
2       C          2014      15
3       D          2017      2
4       E          2013      55
5       F          2017      1
6       G          2017      6
7       H          2017      8

And doing the following query I get the result:

SELECT * FROM exemplo WHERE ano = 2017 ORDER BY nome ASC


ID |    NOME     |  ANO  | REGISTO
----------------------------------
1       B          2017      6
3       D          2017      2
5       F          2017      1
6       G          2017      6
7       H          2017      8

I want to make a UPDATE that increments the value of REGISTO starting with 1 according to alphabetical ordering. That is:

ID |    NOME     |  ANO  | REGISTO
----------------------------------
1       B          2017      1
3       D          2017      2
5       F          2017      3
6       G          2017      4
7       H          2017      5

And repeating the process over the years the end result would be:

SELECT * FROM EXEMPLO ORDER BY id

ID |    NOME     |  ANO  | REGISTO
----------------------------------
0       A          2015      1
1       B          2017      1
2       C          2014      1
3       D          2017      2
4       E          2013      1
5       F          2017      3
6       G          2017      4
7       H          2017      5

I am questioning the process only through SQL, without programming language support.

mysql

asked by anonymous 20.03.2017 / 18:14

1 answer

Display exclude link only if there are no child categories What is it for and how to use include_path in php

score 1 · Accepted Answer

In your case it would be something more or less in this way, having to adapt your real needs.

SET @prev := '';

SET @cnt := 1;

UPDATE exemplo e 
       JOIN (SELECT id, nome, IF(@prev <> ano, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := ano as prev
             FROM   exemplo 
             ORDER  BY ano, nome ASC) e1 
         ON e.id = e1.id 
SET    registo = e1.rank

If you wanted to check the result before, you can run this query:

SET @prev := '';

SET @cnt := 1;

SELECT id, nome, IF(@prev <> ano, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := ano
FROM exemplo
ORDER BY ano, nome ASC