HeidiSQL How to remove duplicate rows (WO#) and only keep most recent time stamp

Question

I am a novice with using HeidiSQL and am hoping someone can help me modify my query to remove duplicate WO# keeping only the most recent date_quoted. An example of what I am looking to achieve is in the picture below, and here is my query:

SELECT
t1.id_workorder AS `WO#`,
t1.date_quoted,
(SELECT `name` FROM crm_customer WHERE id_primary = t1.id_customer) AS `Customer Name`,
t1.labour_total,
t1.labour_pct_discount,
t1.labour_sub_discount,
t1.labour_special_pct_discount,
t1.labour_special_sub_discount,
t1.labour_sub_total,
t1.parts_total,
t1.parts_pct_discount,
t1.parts_sub_discount,
t1.parts_sub_total,
t1.charge_unit_total,
t1.charge_special_total,
t1.charge_freight_total,
t1.tax_total,
t1.grand_total,
t2.id_type
FROM
winnipeg.erp_quoting AS t1

JOIN
  erp_rework AS t2  
ON
  t1.id_workorder = t2.id_workorder

WHERE
  t1.date_quoted > UNIX_TIMESTAMP() - ((86400 * 30) * 30)

ORDER BY
  t1.date_quoted DESC

Picture Example

Answer 1

I think you should be able to do this with simple aggregation. So your query should be:

SELECT
t1.id_workorder AS `WO#`,
MAX(t1.date_quoted),
(SELECT `name` FROM crm_customer WHERE id_primary = t1.id_customer) AS `Customer Name`,
t1.labour_total,
t1.labour_pct_discount,
t1.labour_sub_discount,
t1.labour_special_pct_discount,
t1.labour_special_sub_discount,
t1.labour_sub_total,
t1.parts_total,
t1.parts_pct_discount,
t1.parts_sub_discount,
t1.parts_sub_total,
t1.charge_unit_total,
t1.charge_special_total,
t1.charge_freight_total,
t1.tax_total,
t1.grand_total,
t2.id_type
FROM
winnipeg.erp_quoting AS t1

JOIN
  erp_rework AS t2  
ON
  t1.id_workorder = t2.id_workorder

WHERE
  t1.date_quoted > UNIX_TIMESTAMP() - ((86400 * 30) * 30)

GROUP BY
  t1.id_workorder

ORDER BY
  t1.date_quoted DESC