Hello Andreas,

apologize for the misunderstanding. Hope to clarify now.  For each ID I
want a min and max for each SIDE. I have about 160K records like this:

  label |   id   |     distance                 | side
 -------+--------+-------------------+------
     15 | 119006 |  0.10975569030617 |    1      *m
     14 | 119006 |  0.11844830745091 |    0      *m
     16 | 119006 |  0.17624040731777 |    0
     20 | 119006 |  0.39363711745035 |    0       *M
     19 | 119006 |  0.41205442839764 |    1       *M
     24 | 119006 |  0.35455674575682 |    1
     12 | 23434   |  0.88764543364566 |    0      *M
     31 | 23434   |  0.53456343463466 |    0      *m
     33 | 23434   |  0.23235478697988 |    1      *m/M
     01 | 23434   |  0.59758734628752 |    0
     14 | 129007 |  0.63454675634756 |    0       *m
     13 | 129007 |  0.22345364656788 |    1       *m
     11 | 129007 |  0.86787897897689 |    1       *M
     12 | 129007 |  0.34678678978089 |    1
     19 | 129007 |  0.97897897897654 |    0       *M
(*M maximum for that ID and SIDE, *m minimum for that ID and SIDE)

result should be:
     14 | 119006 |  0.11844830745091 |    0 *m
     20 | 119006 |  0.39363711745035 |    0 *M
     15 | 119006 |  0.10975569030617 |    1 *m
     19 | 119006 |  0.41205442839764 |    1 *M
     31 | 23434   |  0.53456343463466 |    0 *m
     12 | 23434   |  0.88764543364566 |    0 *M
     33 | 23434   |  0.23235478697988 |    1 *m/M
     14 | 129007 |  0.63454675634756 |    0 *m
     19 | 129007 |  0.97897897897654 |    0 *M
     13 | 129007 |  0.22345364656788 |    1 *m
     11 | 129007 |  0.86787897897689 |    1  *M

thanks


- Antonio


On 28 December 2012 15:19, Andreas Kretschmer <andr...@a-kretschmer.de>wrote:

> 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 <antonioparro...@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 <andr...@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 <andr...@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
>

Reply via email to