4

Working with PostgreSQL 9.4, I want a result that takes into consideration only min() (all aggregate functions) values of distance on JOIN or WHERE. But its seems those things are not allowed. So, I am selecting the distance between two points and I want to filter it just to take the min() values into consideration.

To resume the question clearly, suppose these rows:

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" 

How can I extract id, gid, distance and the time_interval that consider just the min(time_interval) values correspondent to that gid. The problem is that I just can see this with this query:

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

The result should be:

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"
4

Use 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

Details:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.