Thread: sql basic question

sql basic question

From
Antonio Parrotta
Date:
Hi All,

I have this table:
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

What I want to achieve is a result table with min and max distance for each side, limiting to 2 (basically the boundaries of each SIDE)

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


Thanks a lot


- Antonio
y

Re: sql basic question

From
Anton Gavazuk
Date:
Do the child Select min, max from... Group by side

Then you can do whatever is required...

Thanks,
Anton

On Dec 28, 2012, at 12:23, Antonio Parrotta <antonioparrotta@gmail.com>
wrote:

Hi All,

I have this table:
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

What I want to achieve is a result table with min and max distance for each
side, limiting to 2 (basically the boundaries of each SIDE)

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


Thanks a lot


- Antonio
y

Re: sql basic question

From
Antonio Parrotta
Date:
Hi Anton,

I need column LABEL  and ID as well. By grouping on SIDE these column cannot be included in the query.

Thanks

- Antonio


On 28 December 2012 12:38, Anton Gavazuk <antongavazuk@gmail.com> wrote:
Do the child Select min, max from... Group by side

Then you can do whatever is required...

Thanks,
Anton

On Dec 28, 2012, at 12:23, Antonio Parrotta <antonioparrotta@gmail.com> wrote:

Hi All,

I have this table:
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

What I want to achieve is a result table with min and max distance for each side, limiting to 2 (basically the boundaries of each SIDE)

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


Thanks a lot


- Antonio
y

Re: sql basic question

From
Anton Gavazuk
Date:
Antonio,

 but then you can do join between minmax select and source table by
distance and get required columns...

Thanks,
Anton

On Dec 28, 2012, at 12:43, Antonio Parrotta <antonioparrotta@gmail.com>
wrote:

Hi Anton,

I need column LABEL  and ID as well. By grouping on SIDE these column
cannot be included in the query.

Thanks

- Antonio


On 28 December 2012 12:38, Anton Gavazuk <antongavazuk@gmail.com> wrote:

> Do the child Select min, max from... Group by side
>
> Then you can do whatever is required...
>
> Thanks,
> Anton
>
> On Dec 28, 2012, at 12:23, Antonio Parrotta <antonioparrotta@gmail.com>
> wrote:
>
> Hi All,
>
> I have this table:
> 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
>
> What I want to achieve is a result table with min and max distance for
> each side, limiting to 2 (basically the boundaries of each SIDE)
>
> 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
>
>
> Thanks a lot
>
>
> - Antonio
> y
>
>

Re: sql basic question

From
Antonio Parrotta
Date:
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

Re: sql basic question

From
Antonio Parrotta
Date:
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

Re: sql basic question

From
Andreas Kretschmer
Date:
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



Re: sql basic question

From
Anton Gavazuk
Date:
Do the child Select min, max from... Group by side

Then you can do whatever is required...

Thanks,
Anton

On Dec 28, 2012, at 12:23, Antonio Parrotta <antonioparrotta@gmail.com> wrote:

Hi All,

I have this table:
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

What I want to achieve is a result table with min and max distance for each side, limiting to 2 (basically the boundaries of each SIDE)

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


Thanks a lot


- Antonio
y

Re: sql basic question

From
Anton Gavazuk
Date:
Antonio,

 but then you can do join between minmax select and source table by distance and get required columns...

Thanks,
Anton

On Dec 28, 2012, at 12:43, Antonio Parrotta <antonioparrotta@gmail.com> wrote:

Hi Anton,

I need column LABEL  and ID as well. By grouping on SIDE these column cannot be included in the query.

Thanks

- Antonio


On 28 December 2012 12:38, Anton Gavazuk <antongavazuk@gmail.com> wrote:
Do the child Select min, max from... Group by side

Then you can do whatever is required...

Thanks,
Anton

On Dec 28, 2012, at 12:23, Antonio Parrotta <antonioparrotta@gmail.com> wrote:

Hi All,

I have this table:
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

What I want to achieve is a result table with min and max distance for each side, limiting to 2 (basically the boundaries of each SIDE)

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


Thanks a lot


- Antonio
y