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