Thread: sql basic question

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

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
>
>

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

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

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