snap to mains

drop table if exists public.test_snap;
select st_closestpoint(wkb_geometry, geom) snapped_geom, geom, wkb_geometry, main_globalid, date, wrkid 
into public.test_snap --call this something better then test_snap
from
(
    select geom, wkb_geometry, globalid main_globalid, date, wrkid, row_number() over(partition by wrkid order by distance) rank
    from
    (
        select st_distance(w.geom, m.wkb_geometry) distance, w.geom, m.wkb_geometry, m.globalid, 
        w.objectid wrkid, w.date 
        from public.water_workorders w
        join water_system.mains m on st_dwithin(w.geom, m.wkb_geometry, 500)
        where w.job_category = 'Main Break/Leak'
        and m.lat_system = 'Main'
    ) as temp
) as closest
where rank = 1;