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

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

A Fidel: link

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)

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

VIEW DEMO IN REXTESTER

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

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(
    SELECT count(1) cantidad,pa.codigo
    FROM presupuestosa pa 
    WHERE pa.obra=18 AND pa.empresa=1 
    Group by pa.codigo
    having count(1)> 1
)tp
    ORDER BY tp.codigo

I hope it serves you.