Date: Fri, 28 Dec 2012 15:21:34 +0100 (CET)
From: Andreas Kretschmer <andreas@a-kretschmer.de>
Reply-To: Andreas Kretschmer <andreas@a-kretschmer.de>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Message-ID: 
 <853734478.423112.1356704494708.JavaMail.open-xchange@ox.ims-firmen.de>
Subject: Fwd: Re: sql basic question
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Medium
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

sorry, only a private replay and not to the list

---------- Urspr=C3=BCngliche 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 m=
ax
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>wr=
ote:
>
> > >
> > > 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=3D*# 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=3D*# select * from (select distinct on (side) label, id, distance,=
 side
> > from
> > foo order by side, distance) a union all (select distinct on (side) lab=
el,
> > id,
> > distance, side from foo order by side, distance desc) order by side des=
c,
> > 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 wit=
h 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 h=
ave a
> join on IDs instead
>
> Thanks
>
> - Antonio
>
>
> On 28 December 2012 13:00, Andreas Kretschmer <andreas@a-kretschmer.de> w=
rote:
> >   >
> >   > 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=3D*# 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=3D*# select * from (select distinct on (side) label, id, distanc=
e, side
> > from
> >   foo order by side, distance) a union all (select distinct on (side) l=
abel,
> > id,
> >   distance, side from foo order by side, distance desc) order by side d=
esc,
> > 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


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql