Mysql – Assignment of first group element – MySQL 4.7

MySQL

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)

Best Answer

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

Related Question