How to get a distinct pair of column values based on minimum of another (computed) column?

Question

I have two tables with point geo data, one (sfc_obs) with weather stations (names), one (smos_grid) with satellite pixels (dggs), and I'm trying to find which dgg is closest to each name using ST_Distance(). I have no trouble getting the distances and sorting, but I'd like to get just the closest match for each name. I think I need to use a GROUP BY OR DISTINCT clause, but everything I've tried has resulted in errors (e.g. GROUP BY name -> "column smos_grid.dgg must appear in the GROUP BY clause...") or resulted in the whole table (below) spit back at me. I have a feeling that one of the related questions may have the answer, but I haven't been able yet to successfully adapt any of them to my situation. I want the distinct pair of name and dgg with the minimum distance. I could (and probably will, in the meantime) use another language to parse the current output, but I'd like to know what the real solution is.

Base query: SELECT sfc_obs.name AS name,smos_grid.dgg AS dgg, ST_Distance(sfc_obs.geom_utm, smos_grid.geom_utm) AS distance FROM sfc_obs,smos_grid ORDER BY name,distance;

Result:

      name       |  dgg   |     distance     
-----------------+--------+------------------
 ALGONA          | 196466 | 7204.37098751625
 ALGONA          | 196465 | 8842.89089934474
 ALGONA          | 196978 |  11010.118792918
 ALGONA          | 195953 | 14495.8081360579
 ALGONA          | 196979 |  16646.802911716
 ALGONA          | 195952 | 20472.9039241652
 ALGONA          | 196977 |  21241.154673217
 ALGONA          | 195954 | 21880.6543096356
 ALGONA          | 196467 | 22540.4472393787  
 ...
 ALGONA          | 212884 | 457182.275288169
 ALGONA          | 213907 | 463002.906164119
 ALGONA          | 213396 | 463254.000442818
 ALGONA          | 213908 | 469680.703686122
 ALGONA          | 213397 | 470914.550849778
 ALGONA          | 213909 | 476963.651054924
 Ames            | 202105 | 3626.03192856493
 Ames            | 201592 | 10236.1588500566
 Ames            | 201593 | 13579.2271071596
 Ames            | 202104 | 15430.7774139333
 Ames            | 202106 |  16638.577453568
 Ames            | 202618 | 17402.7790010433
 Ames            | 202617 | 18506.8761326297
 ...

Desired result:

      name       |  dgg   |     distance     
 -----------------+--------+------------------
 ALGONA          | 196466 | 7204.37098751625
 Ames            | 202105 | 3626.03192856493
 ...

Answer 1

Looks like I just needed to look for some better terms (mainly "nearest neighbor"). I found this site which led me to a solution: http://geeohspatial.blogspot.com/2013/05/k-nearest-neighbor-search-in-postgis.html

Query:

SELECT sfc_obs.name, (SELECT smos_grid.dgg FROM smos_grid ORDER BY sfc_obs.geom_utm <-> smos_grid.geom_utm LIMIT 1), ST_Distance(sfc_obs.geom_utm, (SELECT smos_grid.geom_utm FROM smos_grid ORDER BY sfc_obs.geom_utm <-> smos_grid.geom_utm LIMIT 1)) AS distance FROM sfc_obs ORDER BY name

Result:

      name       |  dgg   |     distance     
-----------------+--------+------------------
 ALGONA          | 196466 | 7204.37098751625
 Ames            | 202105 | 3626.03192856493
 ANKENY          | 203130 | 6122.19132070171
 ATLANTIC        | 202609 | 3634.92253035928
 AUDUBON         | 201585 | 2456.33907602079
 ...