根據一列中的最小值選擇行

使用 PostgreSQL 9.4，我想要一個只考慮min()距離 onJOIN或（所有聚合函式）值的結果WHERE。但似乎那些事情是不允許的。所以，我正在選擇兩點之間的距離，我想過濾它只是為了考慮這些min()值。

為了清楚地恢復問題，假設這些行：

id; gid; distance; time_interval
142; 028; 62; "21:46:00"
200; 028; 53; "08:20:11"
128; 034; 92; "09:24:43"
179; 034; 70; "08:09:34"
194; 034; 92; "05:31:05"
199; 034; 88; "07:15:48"
200; 034; 61; "14:13:43"
202; 035; 24; "17:32:34"
200; 036; 76; "06:02:11"
154; 037; 97; "12:58:58"
154; 040; 30; "11:34:10"
132; 042; 80; "07:01:12"
142; 042; 67; "19:30:21" 

我如何提取id,和gid,只考慮與 that 相對應的值。問題是我只能用這個查詢看到這個：distance``time_interval``min(time_interval)``gid

SELECT id, gid, distance , min(time_interval)
FROM dis
WHERE time_interval = min(time_interval)
GROUP BY id, gid, distance

結果應該是：

id; gid; distance; time_interval
200; 028; 53; "08:20:11"
194; 034; 92; "05:31:05"
202; 035; 24; "17:32:34"
200; 036; 76; "06:02:11"
154; 037; 97; "12:58:58"
154; 040; 30; "11:34:10"
132; 042; 80; "07:01:12"

使用DISTINCT ON：

SELECT DISTINCT ON (gid)
      id, gid, distance, time_interval
FROM   dis
WHERE  time > 0  --  filtered just to take positive values (orig. Q)
ORDER  BY gid, time_interval; -- optionally more expressions to break ties

細節：

• 在每個 GROUP BY 組中選擇第一行？

引用自：https://dba.stackexchange.com/questions/144266