pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql basic question
8+ messages / 3 participants
[nested] [flat]
* sql basic question
@ 2012-12-28 11:23 Antonio Parrotta <antonioparrotta@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Antonio Parrotta @ 2012-12-28 11:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql basic question
@ 2012-12-28 11:38 Anton Gavazuk <antongavazuk@gmail.com>
parent: Antonio Parrotta <antonioparrotta@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Anton Gavazuk @ 2012-12-28 11:38 UTC (permalink / raw)
To: Antonio Parrotta <antonioparrotta@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql basic question
@ 2012-12-28 11:43 Antonio Parrotta <antonioparrotta@gmail.com>
parent: Anton Gavazuk <antongavazuk@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Antonio Parrotta @ 2012-12-28 11:43 UTC (permalink / raw)
To: Anton Gavazuk <antongavazuk@gmail.com>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql basic question
@ 2012-12-28 11:48 Anton Gavazuk <antongavazuk@gmail.com>
parent: Antonio Parrotta <antonioparrotta@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Anton Gavazuk @ 2012-12-28 11:48 UTC (permalink / raw)
To: Antonio Parrotta <antonioparrotta@gmail.com>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql basic question
@ 2012-12-28 14:12 Antonio Parrotta <antonioparrotta@gmail.com>
parent: Antonio Parrotta <antonioparrotta@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Antonio Parrotta @ 2012-12-28 14:12 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; Anton Gavazuk <antongavazuk@gmail.com>
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Fwd: Re: sql basic question
@ 2012-12-28 14:21 Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 0 replies; 8+ messages in thread
From: Andreas Kretschmer @ 2012-12-28 14:21 UTC (permalink / raw)
To: pgsql-sql
sorry, only a private replay and not to the list
---------- Ursprüngliche Nachricht ----------
Von: Andreas Kretschmer <andreas@a-kretschmer.de>
An: Antonio Parrotta <antonioparrotta@gmail.com>
Datum: 28. Dezember 2012 um 15:19
Betreff: Re: [SQL] sql basic question
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql basic question
@ 2012-12-28 14:52 Antonio Parrotta <antonioparrotta@gmail.com>
parent: Antonio Parrotta <antonioparrotta@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Antonio Parrotta @ 2012-12-28 14:52 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql basic question
@ 2012-12-28 15:30 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Antonio Parrotta <antonioparrotta@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Andreas Kretschmer @ 2012-12-28 15:30 UTC (permalink / raw)
To: Antonio Parrotta <antonioparrotta@gmail.com>; 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-12-28 15:30 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-28 11:23 sql basic question Antonio Parrotta <antonioparrotta@gmail.com>
2012-12-28 11:38 ` Anton Gavazuk <antongavazuk@gmail.com>
2012-12-28 11:43 ` Antonio Parrotta <antonioparrotta@gmail.com>
2012-12-28 11:48 ` Anton Gavazuk <antongavazuk@gmail.com>
2012-12-28 14:12 ` Antonio Parrotta <antonioparrotta@gmail.com>
2012-12-28 14:52 ` Antonio Parrotta <antonioparrotta@gmail.com>
2012-12-28 15:30 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2012-12-28 14:21 Fwd: Re: sql basic question Andreas Kretschmer <andreas@a-kretschmer.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox