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 >
