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?
2
Answers
In MySQL 8.0 you can use the
ROW_NUMBER
window function as follows: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".
will return maximal
amount
percodigo_factura
. These data may be used for to select accordingid
value from another copy of the tables pack: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 oneid
for the samecodigo_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.