Filtering using aggregation functions

Question

I would like to filter my table by MIN() function but still keep columns which cant be grouped.

I have table:

+----+----------+----------------------+
| ID | distance |         geom         |
+----+----------+----------------------+
|  1 | 2        | DSDGSAsd23423DSFF    |
|  2 | 11.2     | SXSADVERG678BNDVS4   |
|  2 | 2        | XCZFETEFD567687SDF   |
|  3 | 24       | SADASDSVG3423FD      |
|  3 | 10       | SDFSDFSDF343DFDGF    |
|  4 | 34       | SFDHGHJ546GHJHJHJ    |
|  5 | 22       | SDFSGTHHGHGFHUKJYU45 |
|  6 | 78       | SDFDGDHKIKUI45       |
|  6 | 15       | DSGDHHJGHJKHGKHJKJ65 |
+----+----------+----------------------+

This is what I would like to achieve:

+----+----------+----------------------+
| ID | distance |         geom         |
+----+----------+----------------------+
| 1  |        2 | DSDGSAsd23423DSFF    |
|  2 |        2 | XCZFETEFD567687SDF   |
|  3 |       10 | SDFSDFSDF343DFDGF    |
|  4 |       34 | SFDHGHJ546GHJHJHJ    |
|  5 |       22 | SDFSGTHHGHGFHUKJYU45 |
|  6 |       15 | DSGDHHJGHJKHGKHJKJ65 |
+----+----------+----------------------+

it is possible when I use MIN() on distance column and grouping by ID but then I loose my geom which is essential.

The query looks like this:

SELECT "ID", MIN(distance) AS distance FROM somefile GROUP BY "ID"

the result is:

+----+----------+
| ID | distance |
+----+----------+
| 1  |        2 |
|  2 |        2 |
|  3 |       10 |
|  4 |       34 |
|  5 |       22 |
|  6 |       15 |
+----+----------+

but this is not what I want.

Any suggestions?

If any of the answers helped solve your problem you should consider accepting it. — jpw
– jpw, Commented Sep 18, 2015 at 21:19

jpw · Accepted Answer · 2015-09-16 09:06:39Z

1

One common approach to this is to find the minimum values in a derived table that you join with:

SELECT somefile."ID", somefile.distance, somefile.geom 
FROM somefile 
JOIN (
    SELECT "ID", MIN(distance) AS distance FROM somefile GROUP BY "ID" 
) t ON t.distance = somefile.distance AND t.ID = somefile.ID;

Sample SQL Fiddle

edited Sep 16, 2015 at 9:06

answered Sep 16, 2015 at 9:00

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mihai Ovidiu Drăgoi Over a year ago

Good answer! OP should also take distance ties into consideration (if they're possible).

jpw Over a year ago

@MihaiOvidiuDrăgoi Yes, possibly. If two geoms can have the same distance and id this would return both values which might not be the desired result (but the question doesn't really say).

Patrick · Accepted Answer · 2015-09-16 09:03:07Z

0

You need a window function to do this:

SELECT "ID", distance, geom
FROM (
  SELECT "ID", distance, geom, rank() OVER (PARTITION BY "ID" ORDER BY distance) AS rnk
  FROM somefile) sub
WHERE rnk = 1;

This effectively orders the entire set of rows first by the "ID" value, then by the distance and returns the record for each "ID" where the distance is minimal - no need to do a GROUP BY.

edited Sep 16, 2015 at 9:03

answered Sep 16, 2015 at 9:00

Patrick

33k7 gold badges73 silver badges102 bronze badges

Comments

Amit Tiwari · Accepted Answer · 2015-09-16 09:05:48Z

0

select a.*,b.geom from
(SELECT ID, MIN(distance) AS distance FROM somefile  GROUP BY ID) as a
inner join somefile  as b on a.id=b.id and a.distance=b.distance

answered Sep 16, 2015 at 9:05

Amit Tiwari

3681 silver badge4 bronze badges

Comments

deepthought42 · Accepted Answer · 2015-09-16 12:09:50Z

0

You can use "distinct on" clause of the PostgreSQL.

select distinct on(id) id, distance, geom from table_name order by distance;

I think this is what you are exactly looking for.

For more details on how "distinct on" works, refer the documentation and the example.

But, remember, using "distinct on" does not comply to SQL standards.

answered Sep 16, 2015 at 12:09

deepthought42

112 bronze badges

Collectives™ on Stack Overflow

Filtering using aggregation functions

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related