How to obtain records with a higher amount only in cases where there is duplication in the code fiel-CodePudding

I have the following mysql query:

SELECT pagos.id, codigo_factura as code, pagos.importe as amount 
FROM pagos INNER JOIN m_labores p ON (pagos.id_producto = p.id)
    INNER JOIN maquinas m ON (pagos.id_maquina = m.id) 
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59' 
ORDER BY pagos.id DESC 
LIMIT 10 OFFSET 0

The query returns the following data:

The result I want:

I would like that if there are 2 records with the same code, the query would return only the record whose amount is greater.

I have tried the following:

SELECT pagos.id, codigo_factura as code, pagos.importe as amount 
FROM pagos INNER JOIN m_labores p ON (pagos.id_producto = p.id)
    INNER JOIN maquinas m ON (pagos.id_maquina = m.id) 
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59' 
GROUP BY codigo_factura
HAVING pagos.importe > 0
ORDER BY pagos.id DESC 
LIMIT 10 OFFSET 0

This does not work for me because I want the records with a null amount to continue to appear in cases where code is not duplicated.

Is there any way to do it?

CodePudding user response：

SELECT codigo_factura as code, MAX(pagos.importe) as amount 
FROM pagos 
INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id) 
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59' 
GROUP BY code

will return maximal amount per codigo_factura. These data may be used for to select according id value from another copy of the tables pack:

SELECT MAX(pagos.id) id, codigo_factura as code, pagos.importe as amount 
FROM pagos 
INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id) 
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
AND (codigo_factura, pagos.importe) IN (
    SELECT codigo_factura as code, MAX(pagos.importe) as amount 
    FROM pagos 
    INNER JOIN m_labores p ON (pagos.id_producto = p.id)
    INNER JOIN maquinas m ON (pagos.id_maquina = m.id) 
    WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59' 
    GROUP BY code
    )
GROUP BY code, amount;

MAX() and GROUP BY in outer query allows to fix the situation when the same and at the same time maximal amount value matches more than one id for the same codigo_factura. May be MIN() is more safe for you - edit in this case.

PS. A half of the columns in your code have not table alias, and you have no provided tables structures.. maybe some tables are excess, and the above query can be simplified.

CodePudding user response：

In MySQL 8.0 you can use the ROW_NUMBER window function as follows:

WITH cte AS (
    SELECT pagos.id, codigo_factura as code, pagos.importe as amount,
           ROW_NUMBER() OVER(PARTITION BY codigo_factura ORDER BY pagos.importe DESC) AS rn 
    FROM pagos INNER JOIN m_labores p ON (pagos.id_producto = p.id)
        INNER JOIN maquinas m ON (pagos.id_maquina = m.id) 
    WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59' 
)
SELECT id, code, amount
FROM cte
WHERE rn = 1
ORDER BY pagos.id DESC 
LIMIT 10 OFFSET 0

It will assign a ranking value to each "codigo_factura" by ordering on "pagos.importe" descendently (NULL values come first). Then filtering out all rows whose ranking differs from 1 will remove smaller values for matching "codigo_factura".