Re: select min row in a group

On Fri, 28 Jun 2002 19:48:01 -0400, Gregory Seidman
<gss+pg(at)cs(dot)brown(dot)edu> wrote:
>Nope, I need all the columns. What's really going on is that I have a set
>of points A and a set of points B in a high dimensional space. The table I
>am starting from is really a view on the cartesian product of A and B, with
>the distance between each pair of points. What I'm trying to find is, for
>each point in A, the closest point in B.

Uhh, that's going to take a while, if you have lots of points...

>
>} If you need all the other columns, use this PostgreSQL extension:
>}
>} SELECT DISTINCT ON (ext_id) ext_id, value, ...
>} FROM ValTable
>} ORDER BY ext_id, value;
>
>I'm not sure how I get the minimum value with this construction. Help?

Assuming you have
ext_id | value | something_else
-------------------------------
12 | 500 | aaa
10 | 200 | bbb
12 | 100 | ccc
10 | 400 | ddd

ORDER BY ext_id, value produces
ext_id | value | something_else
-------------------------------
10 | 200 | bbb <--
10 | 400 | ddd
12 | 100 | ccc <--
12 | 500 | aaa

and DISTINCT ON (ext_id) selects the first row from each group of
equal ext_ids (see <-- above), so you get
ext_id | value | something_else
-------------------------------
10 | 200 | bbb
12 | 100 | ccc

Servus
Manfred