CODEWITHSUNDEEP
mysqlsql
user3738031
user3738031

Reputation: 73

How to ignore next duplicated row?

I need your help! I have a table:

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `res` varchar(255) DEFAULT NULL,
  `value` int(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- Records of table

INSERT INTO `table` VALUES (1, 'gold', 44);
INSERT INTO `table` VALUES (2, 'gold', 44);
INSERT INTO `table` VALUES (3, 'gold', 45);
INSERT INTO `table` VALUES (4, 'gold', 46);
INSERT INTO `table` VALUES (5, 'gold', 44);
INSERT INTO `table` VALUES (6, 'gold', 44);
INSERT INTO `table` VALUES (7, 'gold', 44);
INSERT INTO `table` VALUES (8, 'gold', 47);

i need to make SELECT request which will ignored next or previous duplicated rows and i receive data like this:

 - gold:44 (ignored 1 record)
 - gold:45 
 - gold:46 
 - gold:44 (ignored 2 records)
 - gold:47

there is no object which duplicated record will ignore (first,second,last). (i tried to use group by value or distinct but this way removes other records with same value)

Upvotes: 0

Views: 72

Answers (4)

pguetschow
pguetschow

Reputation: 5337

Use Select DISTINCT res, value FROM table ... to avoid redundancy

Upvotes: -1

MatBailie
MatBailie

Reputation: 86765

You can solve this with a gaps and islands solution.
- Normally that involves ROW_NUMBER() which is not present in MySQL
- The solution below mimics ROW_NUMBER() with variables and ORDER BY

Link to example : http://sqlfiddle.com/#!9/32e72/12

SELECT
    MIN(id)   AS id,
    res,
    value
FROM
(
    SELECT
        IF (@res = res AND @val = value, @row := @row + 1, @row := 1)     AS val_ordinal,
        id              AS id,
        res_ordinal     AS res_ordinal,
        @res := res     AS res,
        @val := value   AS value
    FROM
    (
        SELECT
            IF (@res = res                 , @row := @row + 1, @row := 1)     AS res_ordinal,
            id              AS id,
            @res := res     AS res,
            @val := value   AS value
        FROM
            `table`,
        (
            SELECT @row := 0, @res := '', @val := 0
        )
            AS initialiser
        ORDER BY
            res, id
    )
        AS sequenced_res_id,
    (
        SELECT @row := 0, @res := '', @val := 0
    )
        AS initialiser
    ORDER BY
        res, value, id
)
    AS sequenced_res_val_id
GROUP BY
    res,
    value,
    res_ordinal - val_ordinal
ORDER BY
    MIN(id)
;

If I add res_ordinal, val_ordinal and res_ordinal - val_ordinal to your data, it can be seen that you can now differentiate between the two sets of 44

                                                              GROUP

INSERT INTO `table` VALUES ('1', 'gold', '44');   1 - 1 = 0   (Gold, 44, 0)
INSERT INTO `table` VALUES ('2', 'gold', '44');   2 - 2 = 0

INSERT INTO `table` VALUES ('3', 'gold', '45');   3 - 1 = 2   (Gold, 45, 2)

INSERT INTO `table` VALUES ('4', 'gold', '46');   4 - 1 = 3   (Gold, 46, 3)

INSERT INTO `table` VALUES ('5', 'gold', '44');   5 - 3 = 2   (Gold, 44, 2)
INSERT INTO `table` VALUES ('6', 'gold', '44');   6 - 4 = 2
INSERT INTO `table` VALUES ('7', 'gold', '44');   7 - 5 = 2

INSERT INTO `table` VALUES ('8', 'gold', '47');   8 - 1 = 7   (Gold, 47, 7)

NOTE: According to your data I could use id instead of making my own res_ordinal. doing it this way, however, copes with gaps in the id sequence and having multiple different resources. This means that in the following example the two golds are considered to be duplicates of each other...

1   Gold    44     1 - 1 = 0   (Gold, 44, 0)
2   Poop    45     1 - 1 = 0   (Poop, 45, 0)
3   Gold    44     2 - 2 = 0   (Gold, 44, 0)  -- Duplicate
4   Gold    45     3 - 1 = 2   (Gold, 44, 2)

Upvotes: 2

user3738031
user3738031

Reputation: 73

select t1.*
from `table` t1
where not exists ( select 1
                   from `table` t2
                   where t1.id = 1+t2.id 
                   and t1.res = t2.res
                   and t1.value = t2.value
                 );

works fine

Upvotes: 1

DieVeenman
DieVeenman

Reputation: 457

Use the DISTINCT clause to select unique rows like so:

SELECT DISTINCT res, value FROM table

Upvotes: 0

Related Questions