# How to compare the field of a current record with the next, in order to know if it is repeated or not?

3

I have the following query:

``````SELECT pa.codigo,
@rownum:[email protected]+1 AS nro
FROM presupuestosa pa , (SELECT @rownum:=0) r WHERE pa.obra=18
AND pa.empresa=1 ORDER BY codigo ASC
``````

I wish that for each repeated code field, I can save the value 1 in rownum and otherwise rownonum have a value of 0, so I can use those values as undicators. That is, an example like this should yield as a result:

``````|Codigo |nro  |

|3      |   0 |
|5      |   1 |
|5      |   1 |
|7      |   0 |
|8      |   0 |
|9      |   0 |
|11     |   1 |
|11     |   1 |
|12     |   0 |
``````

I tried to use the case statement trying to compare the current row of the code with the next, but I do not know how to do it, if someone has any ideas, I would appreciate it

asked by Danilo 29.07.2018 в 23:22
source

3

Using a subquey that counts the records grouped by work and code, then a left join to translate the totals to 0 or 1.

``````SELECT o.id, o.obra, o.Codigo, IFNULL(n.hayDupes,0) as nro, o.cosa
FROM Table1 o
LEFT JOIN (
SELECT i.Codigo, COUNT(i.Codigo) AS total, 1 AS hayDupes
FROM Table1 i
WHERE i.obra = 18
GROUP BY i.obra, i.codigo
HAVING total>1
) AS n
ON n.Codigo = o.Codigo
WHERE o.obra = 18;
``````

you have to repeat the work id to group well, that maybe you can improve it

explaineishon

In the subquery, the records are grouped by work and code and counted, plus a column `hayDupes` that is always `1` .

The magic is manifested between `HAVING total>1` which causes it to return only duplicate records (2 or more) and the transformation from `hayDupes = NULL` to `0` in the outer query.

This is: in the outer query, the column `nro` is returned with the value of `hayDupes` that always is%% of%, or if the column of the subquery `1` does not exist ( `hayDupes` ) returns `IFNULL` .

Due to the `0` in the outer query only results of the sub query that meet the condition of having the same `LEFT JOIN` are included.

Finally it is filtered by work id.

There may be a more efficient way using group theory to filter the intersection, but it does not come to much = P (the self-join is what is generally used in these cases)

source
3

Since it is a duplicate control, a possible solution would be a query like this:

``````SELECT
p.codigo,
GROUP_CONCAT(p.id SEPARATOR '|') ids,
COUNT(*) total
FROM
presupuestosa p
WHERE p.obra=18 AND p.empresa=1
GROUP BY p.codigo, p.obra, p.empresa;
``````

This query will group by `codigo, obra, empresa` and will throw in the column `total` the number of records in each group. The column `ids` achieved with `GROUP_CONCAT` is a plus, in case you are interested to know for example what the id of each value repeated, will be separated by `|` .

This will allow you simpler and easier to analyze results.

## Proof of concept

I did a test based on real data (I did not put all the records that you show, only a few for reasons of brevity) and I put a case in which there are three duplicates, to show that the query can have an interesting scope: know at once how many duplicates there are ...

``````    CREATE TABLE IF NOT EXISTS presupuesto_20180729
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
codigo INT,
obra INT,
empresa INT
)ENGINE=INNODB;

INSERT INTO presupuesto_20180729 (codigo, obra, empresa) VALUES
(1,1,1),
(2,1,1),
(3,18,1),
(5,18,1),
(5,18,1),
(5,18,1),
(7,18,1),
(11,18,1),
(11,18,1)
;

SET sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT
p.codigo,
GROUP_CONCAT(p.id SEPARATOR '|') ids,
COUNT(*) total
FROM
presupuesto_20180729 p
WHERE p.obra=18 AND p.empresa=1
GROUP BY p.codigo, p.obra, p.empresa;
``````

Result:

``````codigo    ids      total
-------------------------
3         3          1
5         4|5|6      3
7         7          1
11        8|9        2
``````

0

I would like to provide you with this query, it lets you know if the record is repeated more than once, otherwise the word `asc` is redundant because by default it is ordered `asc` .

``````select tp.codigo,@rownum+1 AS nro
from(