Обсуждение: 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