select f.field_id, f.ra_deg, f.dec_deg, f.epoch_mjd
from fields as f inner join 
(select ra_deg, dec_deg, max(epoch_mjd) as latest  from psmops_dgreen_mitll.fields group by ra_deg, dec_deg) as temp 
using (ra_deg, dec_deg)
where f.epoch_mjd = temp.latest


update fields as f
inner join (
    /* Create a table that only contains the last imaged fields of all quads.  */
    /* This is done by grouping on the ra_deg and dec_deg columns and then     */
    /* select the row that corresponds to the last imaged field (>epoch_mjd)   */
    /* and place it into an in memory temporary table .                        */
    /* This temporary table contains the columns:                              */
    /*      field_id    ra_deg      dec_deg     epoch_mjd                      */
    select f.field_id, f.ra_deg, f.dec_deg, f.epoch_mjd
    from fields as f inner join 
    (
        /* Create a table that lists for each distinct ra_deg, dec_deg pair    */
        /* the time at which it was last imaged.                               */
        select ra_deg, dec_deg, max(epoch_mjd) as latest  
        from psmops_dgreen_mitll.fields 
        group by ra_deg, dec_deg
    ) as temp 
    using (ra_deg, dec_deg)
    where f.epoch_mjd = temp.latest 
) as t
using (ra_deg, dec_deg)
set f.parent_id = t.field_id
where f.epoch_mjd != t.epoch_mjd


update fields as f
inner join temp as t
using (ra_deg, dec_deg)
set f.parent_id = t.field_id
where f.epoch_mjd != t.epoch_mjd



SELECT 
    t.tracklet_id AS tracklet_id,
    f.field_id AS field_id,
    d.det_id AS det_id,
    t.gcr_arcsec AS gcr_arcsec,
    t.probability AS probability,
    sum(1) AS ndet, 
    max(d.mag) - min(d.mag) AS deltam,
    k.q AS q,      
    k.e AS e,
    k.known_name AS known_name,      
    f.se_deg AS sol_elong,      
    f.eb_deg AS ecl_lat,      
    f.survey_mode AS survey_mode,
    d.psf_extent,
    d.psf_quality
FROM fields f 
JOIN tracklets t USING(field_id)      
JOIN known k USING(known_id)      
JOIN tracklet_attrib ta USING(tracklet_id)  
JOIN (
    /* Crate a temporary table that contains detections that meet the criteria */
    /* for psf_quality and psf_extent. This temporary table is then joined     */
    /* with the fields, tracklets, known, and tracklet_attrib tables to select */
    /* those tracklets that net the a and magnitude criteria.                  */
    SELECT t.tracklet_id AS tracklet_id,
    d.det_id AS det_id,       
    d.mag AS mag,
    d.filter_id AS filter_id,
    (dr.moments_r1 * dr.moments_r1) / (dr.psf_major * dr.psf_minor) AS psf_extent,
    dr.psf_quality AS psf_quality 
    FROM  fields f 
    JOIN tracklets t USING(field_id)      
    JOIN tracklet_attrib ta USING(tracklet_id)  
    JOIN detections d USING(det_id)
    JOIN det_rawattr_v2 dr USING(det_id)
    WHERE f.nn = 55983 AND t.classification='N'
    AND dr.psf_quality > 0.5
    HAVING psf_extent > 1.0 AND psf_extent < 3.0
    ) AS d USING(det_id)
where k.e > 0 and k.q / (1 - k.e) > 3 and 
and t.v_tot > $minv
group by tracklet_id 
having deltam > .5
and ndet >= 3
and count(distinct d.filter_id) = 1
order by deltam desc

