Skip site navigation (1) Skip section navigation (2)

Re: select min row in a group

From: Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu>
To: PostgreSQL general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: select min row in a group
Date: 2002-06-28 23:48:01
Message-ID: 20020628234800.GA27187@cs.brown.edu (view raw or flat)
Thread:
Lists: pgsql-general
Manfred Koizar sez:
} On Fri, 28 Jun 2002 17:56:17 -0400, Gregory Seidman
} <gss+pg(at)cs(dot)brown(dot)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




In response to

Responses

pgsql-general by date

Next:From: Manfred KoizarDate: 2002-06-29 00:14:55
Subject: Re: select min row in a group
Previous:From: Manfred KoizarDate: 2002-06-28 22:53:55
Subject: Re: serial columns & loads misfeature?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group