Select the highest value of one column per group

Question

I want to show the value of the porcentaje column, but only those records which have the highest id (per item):

SELECT tb_avance.item, tb_avance.id, tb_proceso.porcentaje
    FROM tb_avance_detalle
    INNER JOIN tb_avance on tb_avance_detalle.id_avance=tb_avance.id
    INNER JOIN tb_proceso ON tb_proceso.id_avance_detalle=tb_avance_detalle.id
    INNER JOIN tb_mapa ON tb_mapa.id = tb_avance_detalle.id_mapa
    WHERE tb_mapa.manzana=107 AND tb_mapa.lote=12

Output:

item                       id     porcentaje
--------------------------------------------
EXCAVACION DE FUNDACION    90     100
EXCAVACION DE FUNDACION    91     50
HORMIGON FUNDACIONES       92     90
HORMIGON FUNDACIONES       93     40

I want to show only the marked records:

item                       id     porcentaje
--------------------------------------------
EXCAVACION DE FUNDACION    90     100
EXCAVACION DE FUNDACION    91     50 <------
HORMIGON FUNDACIONES       92     90
HORMIGON FUNDACIONES       93     40 <------

like this

item                       id     porcentaje
--------------------------------------------
EXCAVACION DE FUNDACION    91     50
HORMIGON FUNDACIONES       93     40

Answer 1

You should group by the item, and then you could use group_concat which has an order by clause, and then extract the first value from it:

SELECT     tb_avance.item, 
           max(tb_avance.id) as id,
           cast(group_concat(tb_proceso.porcentaje order by id desc) as unsigned) 
              as porcentaje
FROM       tb_avance_detalle
INNER JOIN tb_avance on tb_avance_detalle.id_avance = tb_avance.id
INNER JOIN tb_proceso ON tb_proceso.id_avance_detalle = tb_avance_detalle.id
INNER JOIN tb_mapa ON tb_mapa.id = tb_avance_detalle.id_mapa
WHERE      tb_mapa.manzana = 107 
       AND tb_mapa.lote = 12
GROUP BY   tb_avance.item