pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHowto??
5+ messages / 5 participants
[nested] [flat]
* Howto??
@ 2003-03-17 13:34 J.C.M. van der Kwast <jkwast@home.nl>
0 siblings, 2 replies; 5+ messages in thread
From: J.C.M. van der Kwast @ 2003-03-17 13:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* howto?
@ 2003-03-17 17:09 Sjors <jkwast@freeler.nl>
0 siblings, 1 reply; 5+ messages in thread
From: Sjors @ 2003-03-17 17:09 UTC (permalink / raw)
To: pgsql-sql
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
This will give me 1 value being the minimum of all distances.
If only done on basis of points and distance I do get the minimum values, but then I don't have the place_id.
How can I get the minimum values of the place_id within in the group of points, being the same?
I've looked and played around with variations round but I can't figure out how to do this. Any suggestions??
greetz sjors
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: howto?
@ 2003-03-17 17:53 Bruno Wolff III <bruno@wolff.to>
parent: Sjors <jkwast@freeler.nl>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2003-03-17 17:53 UTC (permalink / raw)
To: Sjors <jkwast@freeler.nl>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Howto??
@ 2003-03-17 18:57 chester c young <chestercyoung@yahoo.com>
parent: J.C.M. van der Kwast <jkwast@home.nl>
1 sibling, 0 replies; 5+ messages in thread
From: chester c young @ 2003-03-17 18:57 UTC (permalink / raw)
To: J.C.M. van der Kwast <jkwast@home.nl>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Howto??
@ 2003-03-17 19:06 Tomasz Myrta <jasiek@klaster.net>
parent: J.C.M. van der Kwast <jkwast@home.nl>
1 sibling, 0 replies; 5+ messages in thread
From: Tomasz Myrta @ 2003-03-17 19:06 UTC (permalink / raw)
To: J.C.M. van der Kwast <jkwast@home.nl>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-03-17 19:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-17 13:34 Howto?? J.C.M. van der Kwast <jkwast@home.nl>
2003-03-17 18:57 ` chester c young <chestercyoung@yahoo.com>
2003-03-17 19:06 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 17:09 howto? Sjors <jkwast@freeler.nl>
2003-03-17 17:53 ` Re: howto? Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox