From: Andreas Kretschmer <andreas@a-kretschmer.de>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Fwd: Re: sql basic question
Date: Fri, 28 Dec 2012 15:21:34 +0100 (CET)
Message-ID: <853734478.423112.1356704494708.JavaMail.open-xchange@ox.ims-firmen.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
sorry, only a private replay and not to the list
---------- Ursprüngliche Nachricht ----------
Von: Andreas Kretschmer <andreas@a-kretschmer.de>
An: Antonio Parrotta <antonioparrotta@gmail.com>
Datum: 28. Dezember 2012 um 15:19
Betreff: Re: [SQL] sql basic question
Hi,
your question was: "What I want to achieve is a result table with min and max
distance for each side".
Okay, with SIDE in 0,1,-1,2,-2,3,-3 there are exactly 14 possible values for
each SIDE and Min/Max.
If this is wrong, describe your problem better.
Antonio Parrotta <antonioparrotta@gmail.com> hat am 28. Dezember 2012 um 15:12
geschrieben:
> Hi Andreas, Anton,
>
> I did some test and both queries didn't worked. Maybe I was not clear with
> the example provided.
> My table contains more than 160K records with SIDE 0, 1, -1, 2, -2, 3 and
> -3.
> Example provided is a very small subset.
>
> *Andrea's *query is failing because it is getting only distinct SIDEs. The
> query returns just 14 rows.
>
> *Anton's *one because it is joining on distance so merges records without a
> relation (I have many rows with a distance of 0 for example). I need to
> have a join on IDs instead
>
> Thanks
>
> - Antonio
>
>
> On 28 December 2012 13:00, Andreas Kretschmer <andreas@a-kretschmer.de>wrote:
>
> > >
> > > so the result should be:
> > > LABEL ID Distance SIDE
> > > "15"; 119006; 0.10975569030617; 1
> > > "19"; 64056; 0.41205442839764; 1
> > > "14"; 64054; 0.118448307450912; 0
> > > "24"; 119007; 0.59758734628752; 0
> > >
> > >
> >
> >
> >
> > test=*# select * from foo;
> > label | id | distance | side
> > -------+--------+-------------------+------
> > 15 | 119006 | 0.10975569030617 | 1
> > 14 | 64054 | 0.118448307450912 | 0
> > 16 | 64055 | 0.176240407317772 | 0
> > 20 | 64057 | 0.39363711745035 | 0
> > 19 | 64056 | 0.41205442839764 | 1
> > 24 | 119007 | 0.59758734628752 | 0
> > (6 rows)
> >
> > test=*# select * from (select distinct on (side) label, id, distance, side
> > from
> > foo order by side, distance) a union all (select distinct on (side) label,
> > id,
> > distance, side from foo order by side, distance desc) order by side desc,
> > label;
> > label | id | distance | side
> > -------+--------+-------------------+------
> > 15 | 119006 | 0.10975569030617 | 1
> > 19 | 64056 | 0.41205442839764 | 1
> > 14 | 64054 | 0.118448307450912 | 0
> > 24 | 119007 | 0.59758734628752 | 0
> > (4 rows)
> >
> >
> > HTH, Andreas
> >
> Hi Andreas, Anton,
>
> I did some test and both queries didn't worked. Maybe I was not clear with the
> example provided.
> My table contains more than 160K records with SIDE 0, 1, -1, 2, -2, 3 and -3.
> Example provided is a very small subset.
>
> Andrea's query is failing because it is getting only distinct SIDEs. The query
> returns just 14 rows.
>
> Anton's one because it is joining on distance so merges records without a
> relation (I have many rows with a distance of 0 for example). I need to have a
> join on IDs instead
>
> Thanks
>
> - Antonio
>
>
> On 28 December 2012 13:00, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:
> > >
> > > so the result should be:
> > > LABEL ID Distance SIDE
> > > "15"; 119006; 0.10975569030617; 1
> > > "19"; 64056; 0.41205442839764; 1
> > > "14"; 64054; 0.118448307450912; 0
> > > "24"; 119007; 0.59758734628752; 0
> > >
> > >
> >
> >
> >
> > test=*# select * from foo;
> > label | id | distance | side
> > -------+--------+-------------------+------
> > 15 | 119006 | 0.10975569030617 | 1
> > 14 | 64054 | 0.118448307450912 | 0
> > 16 | 64055 | 0.176240407317772 | 0
> > 20 | 64057 | 0.39363711745035 | 0
> > 19 | 64056 | 0.41205442839764 | 1
> > 24 | 119007 | 0.59758734628752 | 0
> > (6 rows)
> >
> > test=*# select * from (select distinct on (side) label, id, distance, side
> > from
> > foo order by side, distance) a union all (select distinct on (side) label,
> > id,
> > distance, side from foo order by side, distance desc) order by side desc,
> > label;
> > label | id | distance | side
> > -------+--------+-------------------+------
> > 15 | 119006 | 0.10975569030617 | 1
> > 19 | 64056 | 0.41205442839764 | 1
> > 14 | 64054 | 0.118448307450912 | 0
> > 24 | 119007 | 0.59758734628752 | 0
> > (4 rows)
> >
> >
> > HTH, Andreas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@a-kretschmer.de
Subject: Re: Fwd: Re: sql basic question
In-Reply-To: <853734478.423112.1356704494708.JavaMail.open-xchange@ox.ims-firmen.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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