Assignment of first group element - MySQL 4.7

Question

I would like to assign the first element of a certain group to 0. The following tables are respectively the original and the final result.

CREATE TABLE IF NOT EXISTS t1 (
 id_ INTEGER NOT NULL,
 value1 INT NOT NULL
)
CREATE TABLE IF NOT EXISTS result (
 id_ INTEGER NOT NULL,
 new_col INT NOT NULL
)
INSERT INTO t1 (id_, value1) VALUES
(1, 1),
(2, 1),
(2, 1),
(3, 1),
(3, 1),
INSERT INTO result (id_, new_col) VALUES
(1, 0),
(2, 0),
(2, 1),
(3, 0),
(3, 1)

• mustaccio: How do you know which of (2,1) and (2,1) is "first" in a group?
• nbk: mysql 4.7 or 5.7?

Answer 1

This will run so only in MySQL, in MariaDB you will need a LIMIt in the inner SELECT

The principal thing is the ORDER of the inner SRLCT, which determines , which rownumber (rn) it will get. here i choose ORDER BY id to achieve your result. But as mustachio pointed out you have to define an order, so that this will work

CREATE TABLE IF NOT EXISTS t1 (
 id_ INTEGER NOT NULL,
 value1 INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 1),
(2, 1),
(2, 1),
(3, 1),
(3, 1)

SELECT id_, value1
FROM (SELECT
 IF(id_ = @id,@rn := @rn +1,@rn := 0) value1
 ,@id := id_ as id_
FROM t1,(SELECT @id := 0 ,@rn := -1) t2
ORDER BY id_) t3

id_ | value1
--: | -----:
 1 | 0
 2 | 0
 2 | 1
 3 | 0
 3 | 1

db<>fiddle here

• Bruno Lobo: When does the reset of @rn from 0 to -1 again occur?
• nbk: in Mysql, you have to initialize the user defined @ variables, else it will not work. MySQL processes first the from clazse, where bothh variables get initialized and then it fills them row by row
• Bruno Lobo: What is the variable @tn ?
• nbk: only a typo of my side