Thread: select min row in a group

This is really an SQL question, not specific to PostgreSQL. I have a table:

ValTable
======
id    int
ext_id    int
value    float

For each ext_id (which references something in another table), I want to
find the row with the minimum value. The best I can come up with is:

SELECT * FROM ValTable AS v
WHERE v.value = (
    SELECT DISTINCT min(value) FROM ValTable
    WHERE ext_id = v.ext_id
    )

I feel like there has to be a way to do it without a nested query, probably
using GROUP BY. Any thoughts?

--Greg

On Fri, 28 Jun 2002 17:56:17 -0400, Gregory Seidman
<gss+pg@cs.brown.edu> wrote:
>This is really an SQL question, not specific to PostgreSQL.
Greg,

but the answer is specific to PostgreSQL :-)

>For each ext_id (which references something in another table), I want to
>find the row with the minimum value.
>I feel like there has to be a way to do it without a nested query, probably
>using GROUP BY. Any thoughts?

If you only need ext_id and value, this should work with most SQL
dialects:

    SELECT ext_id, min(value)
    FROM ValTable
    GROUP BY ext_id;

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;

Servus
 Manfred

Manfred Koizar sez:
} On Fri, 28 Jun 2002 17:56:17 -0400, Gregory Seidman
} <gss+pg@cs.brown.edu> wrote:
} >This is really an SQL question, not specific to PostgreSQL.
} Greg,
}
} but the answer is specific to PostgreSQL :-)
}
} >For each ext_id (which references something in another table), I want to
} >find the row with the minimum value.
} >I feel like there has to be a way to do it without a nested query, probably
} >using GROUP BY. Any thoughts?
}
} If you only need ext_id and value, this should work with most SQL
} dialects:
}
}     SELECT ext_id, min(value)
}     FROM ValTable
}     GROUP BY ext_id;

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.

} 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?

} Servus
}  Manfred
--Greg

On Fri, 28 Jun 2002 19:48:01 -0400, Gregory Seidman
<gss+pg@cs.brown.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