From jkwast@home.nl Mon Mar  17 13:34:32 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from mail6-sh.home.nl (mail6.home.nl [213.51.128.20])
	by postgresql.org (Postfix) with ESMTP id E5D08475AE5
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 08:34:32 -0500 (EST)
Received: from cc299057a ([217.121.167.4]) by mail6-sh.home.nl
	(InterMail vM.5.01.05.17 201-253-122-126-117-20021021) with SMTP
	id <20030317133435.DDWD21331.mail6-sh.home.nl@cc299057a>
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 14:34:35 +0100
Message-ID: <000801c2ec89$f70eeb60$04a779d9@cc299057a>
From: "J.C.M. van der Kwast" <jkwast@home.nl>
To: <pgsql-sql@postgresql.org>
Subject: Howto??
Date: Mon, 17 Mar 2003 14:34:41 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0005_01C2EC92.587F3FF0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Archive-Number: 200303/405
X-Sequence-Number: 12444

This is a multi-part message in MIME format.

------=_NextPart_000_0005_01C2EC92.587F3FF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

After trying for a week now I've come to the conclusion that help is needed=
. I've got the following problem:
I have a table with id column, another id (id2) column and a value column. =
For each id there can be more id2. I want id2 for id where the value is min=
. I tried several things with having but it doesn't produce what I need. Ex=
ample:

select id, id2, min(value) from table
group by id
having count (*) >1 and min(value) =3D (select min(value) from table)

Now it complains about id2 not being part of aggregate.=20=20
In order to get the min value I have to group by id, right? But because id2=
 is unique it won't do min on those values, right? So how do you tackle a p=
roblem like that? Any suggestions?

thanks sjors
------=_NextPart_000_0005_01C2EC92.587F3FF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1126" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>After trying for a week now I've come to t=
he=20
conclusion that help is needed. I've got the following problem:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I have a table with id column, another id=
=20
(id2)&nbsp;column and a value column. For each id there can be&nbsp;more id=
2. I=20
want id2 for id where the value is min. I tried several things with having =
but=20
it doesn't produce what&nbsp;I need. Example:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select id, id2, min(value)&nbsp;from=20
table</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>group by id</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>having count (*) &gt;1 and&nbsp;min(value)=
 =3D=20
(select min(value) from table)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Now it complains about id2 not being part =
of=20
aggregate. &nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>In order to get the min value I have to gr=
oup by=20
id, right?&nbsp;But because id2 is unique it won't do min on those values,=
=20
right?&nbsp;So how do you tackle a problem like that? Any=20
suggestions?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thanks sjors</FONT></DIV></BODY></HTML>

------=_NextPart_000_0005_01C2EC92.587F3FF0--




From jkwast@freeler.nl Mon Mar  17 17:09:21 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from mail6-sh.home.nl (mail6.home.nl [213.51.128.20])
	by postgresql.org (Postfix) with ESMTP id 5BEE3476105
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 12:09:21 -0500 (EST)
Received: from cc299057a ([217.121.167.4]) by mail6-sh.home.nl
	(InterMail vM.5.01.05.17 201-253-122-126-117-20021021) with SMTP
	id <20030317170926.EAGT21331.mail6-sh.home.nl@cc299057a>
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 18:09:26 +0100
Message-ID: <008901c2eca7$fa181110$04a779d9@cc299057a>
From: "Sjors" <jkwast@freeler.nl>
To: <pgsql-sql@postgresql.org>
Subject: howto?
Date: Mon, 17 Mar 2003 18:09:31 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0086_01C2ECB0.5BB19880"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Archive-Number: 200303/400
X-Sequence-Number: 12439

This is a multi-part message in MIME format.

------=_NextPart_000_0086_01C2ECB0.5BB19880
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

Being not able to grasp in full sql, I've decided to call on the list. I've=
 got a table with points, place_id and distance between points and place_id=
. For each point there can be more place_id's but I want to selesct the one=
 with the minimum distance. Now I can group by the points and select the mi=
n(distance) but I can't do this if place_id is involved. I used a statment =
like this:

Select points, place_id, distance from table
group by points, place_id, distance
having count(*)>1 and distance=3D(select min(distance) from table

This will give me 1 value being the minimum of all distances.=20
If only done on basis of points and distance I do get the minimum values, b=
ut then I don't have the place_id.=20
How can I get the minimum values of the place_id within in the group of poi=
nts, being the same?=20
I've looked and played around with variations round but I can't figure out =
how to do this. Any suggestions??=20=20

greetz sjors
------=_NextPart_000_0086_01C2ECB0.5BB19880
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1126" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Being not able to grasp in full sql, I've =
decided=20
to call on the list. I've got a table with points, place_id and distance be=
tween=20
points and place_id.&nbsp;For each point there can be more place_id's but I=
 want=20
to selesct the one with&nbsp;the minimum distance. Now I can group by the p=
oints=20
and select the min(distance) but I can't do this if place_id is involved. I=
 used=20
a statment like this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Select points, place_id, distance from=20
table</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>group by points, place_id, distance</FONT>=
</DIV>
<DIV><FONT face=3DArial size=3D2>having&nbsp;count(*)&gt;1 and distance=3D(=
select=20
min(distance) from table</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>This will&nbsp;give me&nbsp;1 value being =
the=20
minimum of all distances. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>If only done on basis of points and distan=
ce I do=20
get the minimum values, but then I don't have the place_id. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>How can I&nbsp;get the minimum values of t=
he=20
place_id within in the group of points, being the same? </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I've looked and played around with variati=
ons=20
round&nbsp;but I can't figure out how to do this. Any=20
suggestions??</FONT>&nbsp;&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>greetz sjors</FONT></DIV></BODY></HTML>

------=_NextPart_000_0086_01C2ECB0.5BB19880--




From bruno@wolff.to Mon Mar  17 17:39:06 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id 43048474E53
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 12:39:06 -0500 (EST)
Received: (qmail 21317 invoked by uid 500); 17 Mar 2003 17:53:33 -0000
Date: Mon, 17 Mar 2003 11:53:33 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Sjors <jkwast@freeler.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: howto?
Message-ID: <20030317175333.GA21282@wolff.to>
Mail-Followup-To: Sjors <jkwast@freeler.nl>, pgsql-sql@postgresql.org
References: <008901c2eca7$fa181110$04a779d9@cc299057a>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <008901c2eca7$fa181110$04a779d9@cc299057a>
User-Agent: Mutt/1.3.25i
X-Archive-Number: 200303/401
X-Sequence-Number: 12440

On Mon, Mar 17, 2003 at 18:09:31 +0100,
  Sjors <jkwast@freeler.nl> wrote:
> Hi,
> 
> Being not able to grasp in full sql, I've decided to call on the list. I've got a table with points, place_id and distance between points and place_id. For each point there can be more place_id's but I want to selesct the one with the minimum distance. Now I can group by the points and select the min(distance) but I can't do this if place_id is involved. I used a statment like this:
> 
> Select points, place_id, distance from table
> group by points, place_id, distance
> having count(*)>1 and distance=(select min(distance) from table

How about:
select distinct on (points) points, place_id, distance from table
order by points, distance;




From chestercyoung@yahoo.com Mon Mar  17 18:57:52 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from web12702.mail.yahoo.com (web12702.mail.yahoo.com
	[216.136.173.239])
	by postgresql.org (Postfix) with SMTP id 9B917474E53
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 13:57:52 -0500 (EST)
Message-ID: <20030317185752.59785.qmail@web12702.mail.yahoo.com>
Received: from [63.230.97.206] by web12702.mail.yahoo.com via HTTP;
	Mon, 17 Mar 2003 10:57:52 PST
Date: Mon, 17 Mar 2003 10:57:52 -0800 (PST)
From: chester c young <chestercyoung@yahoo.com>
Subject: Re: Howto??
To: "J.C.M. van der Kwast" <jkwast@home.nl>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <000801c2ec89$f70eeb60$04a779d9@cc299057a>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200303/408
X-Sequence-Number: 12447

> I have a table with id column, another id (id2) column and a value
> column. For each id there can be more id2. I want id2 for id where
> the value is min. 
> thanks sjors

select distinct id2 from tab
where id=x and value = (select max(value) from tab where id=x)


__________________________________________________
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online
http://webhosting.yahoo.com



From jasiek@klaster.net Mon Mar  17 19:05:43 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 5B601476105
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 14:05:43 -0500 (EST)
Received: from klaster.net (ph24.krakow.cvx.ppp.tpnet.pl [217.99.208.24])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 2C52C6A1DE; Mon, 17 Mar 2003 20:05:38 +0100 (CET)
Message-ID: <3E761CC1.6000906@klaster.net>
Date: Mon, 17 Mar 2003 20:06:41 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.3) Gecko/20030312
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: "J.C.M. van der Kwast" <jkwast@home.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: Howto??
References: <000801c2ec89$f70eeb60$04a779d9@cc299057a>
In-Reply-To: <000801c2ec89$f70eeb60$04a779d9@cc299057a>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200303/412
X-Sequence-Number: 12451

Uz.ytkownik J.C.M. van der Kwast napisa?:
> Hi,
>  
> After trying for a week now I've come to the conclusion that help is 
> needed. I've got the following problem:
> I have a table with id column, another id (id2) column and a value 
> column. For each id there can be more id2. I want id2 for id where the 
> value is min. I tried several things with having but it doesn't produce 
> what I need. Example:
>  
> select id, id2, min(value) from table
> group by id
> having count (*) >1 and min(value) = (select min(value) from table)
>  
> Now it complains about id2 not being part of aggregate.  
> In order to get the min value I have to group by id, right? But because 
> id2 is unique it won't do min on those values, right? So how do you 
> tackle a problem like that? Any suggestions?
>  
> thanks sjors

select distinct on(id) id2 from table
order by id,value;

Regards,
Tomasz Myrta