Re: sql basic question

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
>