Re: sql basic question - Mailing list pgsql-sql
| From | Andreas Kretschmer |
|---|---|
| Subject | Re: sql basic question |
| Date | |
| Msg-id | 1392630898.423213.1356708637646.JavaMail.open-xchange@ox.ims-firmen.de Whole thread Raw |
| In response to | Re: sql basic question (Antonio Parrotta <antonioparrotta@gmail.com>) |
| List | pgsql-sql |
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 <antonioparrotta@gmail.com> 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 <andreas@a-kretschmer.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@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 > > > 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@a-kretschmer.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@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