mysql - Self joining table view -
i have table containing non-unique leads, need group contain unique, recent (column date ) leads. id lead_id status date ----- ------ -- ------------------- 26199 666842 ok 2013-06-19 12:00:09 56199 376842 ok 2013-06-19 12:00:09 58322 376842 ok 2013-06-21 12:11:59 60357 376842 ok 2013-06-24 12:22:00 61431 376842 ok 2013-06-25 12:18:02 62365 376842 ok 2013-06-26 12:16:04 63202 376842 ok 2013-06-27 12:14:08 63983 376842 er 2013-06-28 12:12:06 so in example above should have 2 leads result: id 26199 , 63983 both ones max(date) while being group by lead_id. i tried left joins, max , group aggregation, don't know i'm doing wrong. select a.lead_id, max(a.created) created rawlead left join rawlead b on b.created = a.created group a.lead_id unfortunatelly cannot use subqueries, cause need present them in view. no subqueries :) select l1.* lead l1 left join lead l2 on l1.date ...