Assignment of first group element – MySQL 4.7

All we need is an easy explanation of the problem, so here it is.

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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply