Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tobwy-0001li-7f for pgsql-sql@arkaria.postgresql.org; Fri, 28 Dec 2012 15:34:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Tobwx-0004qo-6l for pgsql-sql@arkaria.postgresql.org; Fri, 28 Dec 2012 15:34:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tobwj-0004fx-MK for pgsql-sql@postgresql.org; Fri, 28 Dec 2012 15:34:05 +0000 Received: from mailout02.ims-firmen.de ([213.174.32.97]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Tobwh-00044c-19 for pgsql-sql@postgresql.org; Fri, 28 Dec 2012 15:34:05 +0000 Received: from mailin01.ims-firmen.de ([192.168.1.141]) by mailout02.ims-firmen.de with esmtp (envelope-from ) id 1Tobwf-0007Z4-lx; Fri, 28 Dec 2012 16:34:01 +0100 Received: from [213.174.32.191] (helo=oxweb01.ims-firmen.de) by mailin01.ims-firmen.de with esmtpsa (TLSv1:RC4-MD5:128) (envelope-from ) id 1Tobwf-0007Os-2T; Fri, 28 Dec 2012 16:34:01 +0100 Date: Fri, 28 Dec 2012 16:30:37 +0100 (CET) From: Andreas Kretschmer Reply-To: Andreas Kretschmer To: Antonio Parrotta , pgsql-sql@postgresql.org Message-ID: <1392630898.423213.1356708637646.JavaMail.open-xchange@ox.ims-firmen.de> In-Reply-To: References: <1557302072.422833.1356696053149.JavaMail.open-xchange@ox.ims-firmen.de> <1463175085.423102.1356704358689.JavaMail.open-xchange@ox.ims-firmen.de> Subject: Re: sql basic question MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Medium X-Mailer: Open-Xchange Mailer v6.20.6-Rev4 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org Maybe something like: test=*# select * from foo; label | id | distance | side -------+--------+------------------+------ 15 | 119006 | 0.10975569030617 | 1 14 | 119006 | 0.11844830745091 | 0 16 | 119006 | 0.17624040731777 | 0 20 | 119006 | 0.39363711745035 | 0 19 | 119006 | 0.41205442839764 | 1 24 | 119006 | 0.35455674575682 | 1 12 | 23434 | 0.88764543364566 | 0 31 | 23434 | 0.53456343463466 | 0 33 | 23434 | 0.23235478697988 | 1 1 | 23434 | 0.59758734628752 | 0 14 | 129007 | 0.63454675634756 | 0 13 | 129007 | 0.22345364656788 | 1 11 | 129007 | 0.86787897897689 | 1 12 | 129007 | 0.34678678978089 | 1 19 | 129007 | 0.97897897897654 | 0 (15 rows) test=*# select * from (select distinct on (id, side) label, id, distance, side, 'm'::text as min_max from foo order by id, side, distance) a union all (select distinct on (id, side) label, id, distance, side, 'M' as min_max from foo order by id, side, distance desc); label | id | distance | side | min_max -------+--------+------------------+------+--------- 31 | 23434 | 0.53456343463466 | 0 | m 33 | 23434 | 0.23235478697988 | 1 | m 14 | 119006 | 0.11844830745091 | 0 | m 15 | 119006 | 0.10975569030617 | 1 | m 14 | 129007 | 0.63454675634756 | 0 | m 13 | 129007 | 0.22345364656788 | 1 | m 12 | 23434 | 0.88764543364566 | 0 | M 33 | 23434 | 0.23235478697988 | 1 | M 20 | 119006 | 0.39363711745035 | 0 | M 19 | 119006 | 0.41205442839764 | 1 | M 19 | 129007 | 0.97897897897654 | 0 | M 11 | 129007 | 0.86787897897689 | 1 | M (12 rows) Better? Antonio Parrotta hat am 28. Dezember 2012 um 15:52 geschrieben: > 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 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 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 > >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 > > 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 > > > 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 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@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 < > > > 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 > > > > > > > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql