Re: sql basic question

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 <andreas(at)a-kretschmer(dot)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 <antonioparrotta(at)gmail(dot)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(at)a-kretschmer(dot)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(at)a-kretschmer(dot)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
>