MySQL SELECT JOIN max value

This is how you can JOIN a max value in a SELECT.

SELECT aaldw.clientnid, aaldw.proj_type, aaldw_max.max_day AS maxday, SUM( month_alloc ) AS month_alloc, SUM( month_log ) AS month_log, ROUND( SUM( month_log ) *100 / SUM( month_alloc ) ) AS month_eff, SUM( tot_alloc ) AS tot_alloc, SUM( tot_log ) AS tot_log, ROUND( SUM( tot_log ) *100 / SUM( tot_alloc ) ) AS tot_eff
FROM cc2_accum_alloc_log_dw aaldw, (SELECT tasknid, projnid, MAX(day) AS max_day
FROM cc2_accum_alloc_log_dw aaldw2 WHERE day < 20110818 GROUP BY tasknid) aaldw_max
LEFT JOIN node ON node.nid = aaldw_max.projnid
WHERE aaldw.id = (SELECT id FROM cc2_accum_alloc_log_dw WHERE tasknid = aaldw_max.tasknid AND day = aaldw_max.max_day ORDER BY id DESC LIMIT 1 )AND node.status = 1
GROUP BY clientnid, proj_type

SELECT n.nid, n.title, o.*, mozd.*, pa.*, ld.*, el.*, da.*, c.*, dd.*, og.*, gr.title AS group_title, td.*, MAX(co.timestamp) AS last_comment FROM node n
LEFT JOIN content_type_opportunity o ON(o.nid=n.nid)
LEFT JOIN content_field_o_seomoz_date mozd ON(mozd.nid=n.nid)
LEFT JOIN content_field_o_page_authority pa ON(pa.nid=n.nid)
LEFT JOIN content_field_o_linking_domains ld ON(ld.nid=n.nid)
LEFT JOIN content_field_o_external_links el ON(el.nid=n.nid)
LEFT JOIN content_field_o_domain_authority da ON(da.nid=n.nid)
LEFT JOIN content_field_o_contact c ON(c.nid=n.nid)
LEFT JOIN content_field_date dd ON(dd.nid=n.nid)
                            LEFT JOIN comments co ON(co.nid=n.nid AND co.cid IN(SELECT cid FROM comments ORDER BY `timestamp` DESC))
LEFT JOIN og_ancestry og ON(og.nid=n.nid)
LEFT JOIN node gr ON(gr.nid=og.group_nid)
LEFT JOIN term_node tn ON(tn.nid=n.nid AND tn.tid IN(SELECT tid FROM term_data WHERE vid = 5))
LEFT JOIN term_data td ON(td.tid=tn.tid)
WHERE n.type = 'opportunity'  GROUP BY n.nid ORDER BY n.nid DESC

<?php
SELECT n_bu
.nid AS bu_nid, n_bu.title AS bu_title, tn2.tid AS bu_tid, ctbs.nid AS bs_nid, ctbj.nid AS bj_nid, field_bj_starttime_value, tn.tid AS bj_tid, n_bj.nid AS bj_nid, n_bj.title AS bj_title,
ctbs.field_backupsetid_value AS bs_id, n_bs.title AS bs_title, ctbj.field_bj_destination_id_value AS obs_destination_id 
FROM content_type_backup_set ctbs
LEFT JOIN content_type_backup_job ctbj ON
(ctbj.field_backupset_nid = ctbs.nid
                                       
AND ctbj.field_bj_starttime_value = (SELECT MAX(ctbj2.field_bj_starttime_value) FROM content_type_backup_job AS ctbj2
                                                                            WHERE ctbj2
.field_backupset_nid = ctbs.nid))

LEFT JOIN node n_bj ON (n_bj.nid = ctbj.nid)
LEFT JOIN term_node tn ON (tn.nid = ctbj.nid AND tn.tid IN (1,2,3,4,5,6,7,241,244,246,250,251,252,254,255,256,257,258))
LEFT JOIN node n_bs ON (ctbs.nid = n_bs.nid)
LEFT JOIN content_type_backup_user ctbu ON (ctbu.nid = ctbs.field_backupuser_nid)
LEFT JOIN node n_bu ON (n_bu.nid = ctbu.nid)
LEFT JOIN term_node tn2 ON (tn2.nid = ctbu.nid AND tn2.tid = 217)

WHERE UNIX_TIMESTAMP(ctbj.field_bj_starttime_value) > 1546031650 AND ctbj.`field_backupset_nid`IN ( 188576, 188579, 188574, 17080, 17093, 17095,28932)

GROUP BY field_backupset_nid
ORDER BY ctbj
.field_bj_starttime_value DESC
?>
Knowledge keywords: