pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: challenging query
5+ messages / 3 participants
[nested] [flat]
* challenging query
@ 2001-10-03 17:33 Chris Ruffin <c.ruffin@ieee.org>
1970-01-01 00:00 ` Re: challenging query Haller Christoph <ch@rodos.fzk.de>
1970-01-01 00:00 ` Re: challenging query Haller Christoph <ch@rodos.fzk.de>
0 siblings, 2 replies; 5+ messages in thread
From: c.ruffin@ieee.org @ 2001-10-03 17:33 UTC (permalink / raw)
To: pgsql-sql
Consider the following table:
A B C D select?
-------------------------------
1 FOO A1 100 n
1 BAR Z2 100 n
2 FOO A1 101 y
2 BAR Z2 101 y
3 FOO A1 102 y
4 BAR Z2 99 y
5 FOO A1 99 n
6 BAR Z2 98 n
7 FOO AB 103 y
7 BAR ZY 103 y
This table has the idea of "groups", that is, a group is defined as
all of the words from B that have the same number A. The values in
column C also matter- we want to select both groups A=7 and A=1 since
they contain different values C. Note that the groups defined by A=1
and A=3 are distinct- they do not contain the same number of words
from B, so we want to select them both. Also note that D is datetime,
and all the rows with the same number A will have the same D (this is
actually ensured by a single row in another table.)
I want to select all of the numbers A which define distinct groups and
have the highest datetime D. Is this possible in a SQL query?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: challenging query
2001-10-03 17:33 challenging query Chris Ruffin <c.ruffin@ieee.org>
@ 1970-01-01 00:00 ` Haller Christoph <ch@rodos.fzk.de>
1 sibling, 0 replies; 5+ messages in thread
From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
To: c.ruffin@ieee.org; +Cc: pgsql-sql
>
> Consider the following table:
>
> A B C D select?
> -------------------------------
> 1 FOO A1 100 n
> 1 BAR Z2 100 n
> 2 FOO A1 101 y
> 2 BAR Z2 101 y
> 3 FOO A1 102 y
> 4 BAR Z2 99 y
> 5 FOO A1 99 n
> 6 BAR Z2 98 n
> 7 FOO AB 103 y
> 7 BAR ZY 103 y
>
> This table has the idea of "groups", that is, a group is defined as
> all of the words from B that have the same number A. The values in
> column C also matter- we want to select both groups A=7 and A=1 since
> they contain different values C. Note that the groups defined by A=1
> and A=3 are distinct- they do not contain the same number of words
> from B, so we want to select them both. Also note that D is datetime,
> and all the rows with the same number A will have the same D (this is
> actually ensured by a single row in another table.)
>
> I want to select all of the numbers A which define distinct groups and
> have the highest datetime D. Is this possible in a SQL query?
Sorry, there is an inconsistency in your question, at least to me.
In the table's last column you say you want to select
A in (2,3,4,7)
but in the epilogue you say you want to select
A in (7,1,1,3)
What did I miss?
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: challenging query
2001-10-03 17:33 challenging query Chris Ruffin <c.ruffin@ieee.org>
@ 1970-01-01 00:00 ` Haller Christoph <ch@rodos.fzk.de>
2001-10-06 09:54 ` Re: challenging query Masaru Sugawara <rk73@echna.ne.jp>
1 sibling, 1 reply; 5+ messages in thread
From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
To: c.ruffin@ieee.org; +Cc: pgsql-sql
>
> Consider the following table:
>
> A B C D select?
> -------------------------------
> 1 FOO A1 100 n
> 1 BAR Z2 100 n
> 2 FOO A1 101 y
> 2 BAR Z2 101 y
> 3 FOO A1 102 y
> 4 BAR Z2 99 y
> 5 FOO A1 99 n
> 6 BAR Z2 98 n
> 7 FOO AB 103 y
> 7 BAR ZY 103 y
>
> This table has the idea of "groups", that is, a group is defined as
> all of the words from B that have the same number A. The values in
> column C also matter- we want to select both groups A=7 and A=1 since
> they contain different values C. Note that the groups defined by A=1
> and A=3 are distinct- they do not contain the same number of words
> from B, so we want to select them both. Also note that D is datetime,
> and all the rows with the same number A will have the same D (this is
> actually ensured by a single row in another table.)
>
> I want to select all of the numbers A which define distinct groups and
> have the highest datetime D. Is this possible in a SQL query?
>
Now that I've read your request more attentively, I understand what
you want. But I have to admit I have no idea how to word the query,
I even don't know if it's possible at all.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: challenging query
2001-10-03 17:33 challenging query Chris Ruffin <c.ruffin@ieee.org>
1970-01-01 00:00 ` Re: challenging query Haller Christoph <ch@rodos.fzk.de>
@ 2001-10-06 09:54 ` Masaru Sugawara <rk73@echna.ne.jp>
2001-10-30 17:57 ` Re: challenging query Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 1 reply; 5+ messages in thread
From: Masaru Sugawara @ 2001-10-06 09:54 UTC (permalink / raw)
To: pgsql-sql
On Fri, 05 Oct 2001 17:03:41 METDST
Haller Christoph wrote:
> >
> > Consider the following table:
> >
> > A B C D select?
> > -------------------------------
> > 1 FOO A1 100 n
> > 1 BAR Z2 100 n
> > 2 FOO A1 101 y
> > 2 BAR Z2 101 y
> > 3 FOO A1 102 y
> > 4 BAR Z2 99 y
> > 5 FOO A1 99 n
> > 6 BAR Z2 98 n
> > 7 FOO AB 103 y
> > 7 BAR ZY 103 y
> >
> > This table has the idea of "groups", that is, a group is defined as
> > all of the words from B that have the same number A. The values in
> > column C also matter- we want to select both groups A=7 and A=1 since
> > they contain different values C. Note that the groups defined by A=1
> > and A=3 are distinct- they do not contain the same number of words
> > from B, so we want to select them both. Also note that D is datetime,
> > and all the rows with the same number A will have the same D (this is
> > actually ensured by a single row in another table.)
> >
> > I want to select all of the numbers A which define distinct groups and
> > have the highest datetime D. Is this possible in a SQL query?
> >
> Now that I've read your request more attentively, I understand what
> you want. But I have to admit I have no idea how to word the query,
> I even don't know if it's possible at all.
> Regards, Christoph
>
I also haven't satisfactorily understood the mean of the epilogue,
but I probably think he wanted to account for the following table
which is separated into "groups".
A B C D select?
------------------------------------
1 FOO A1 100 n
1 BAR Z2 100 n
2 FOO A1 101 y
2 BAR Z2 101 y
---------------------------------
5 FOO A1 99 n
3 FOO A1 102 y
---------------------------------
6 BAR Z2 98 n
4 BAR Z2 99 y
---------------------------------
7 FOO AB 103 y
7 BAR ZY 103 y
for instance:
select u0.A, u0.B, u0.C, u0.D
from (select t0.*, t1.cnt
from (select a, count(*) as cnt
from test_table
group by a ) as t1
inner join test_table as t0
on(t0.a = t1.a)
) as u0
where not exists (select u1.*
from (select t0.*, t1.cnt
from (select a, count(*) as cnt
from test_table
group by a ) as t1
inner join test_table as t0
on(t0.a = t1.a)
) as u1
where u1.cnt = u0.cnt
and u1.a != u0.a
and u1.d > u0.d
and u1.b = u0.b
and u1.c = u0.c
)
;
a | b | c | d
---+-----+----+-----
2 | BAR | Z2 | 101
2 | FOO | A1 | 101
3 | FOO | A1 | 102
4 | BAR | Z2 | 99
7 | BAR | ZY | 105
7 | FOO | AB | 105
(6 rows)
Have a nice weekend!
----------------------
Masaru Sugawara
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: challenging query
2001-10-03 17:33 challenging query Chris Ruffin <c.ruffin@ieee.org>
1970-01-01 00:00 ` Re: challenging query Haller Christoph <ch@rodos.fzk.de>
2001-10-06 09:54 ` Re: challenging query Masaru Sugawara <rk73@echna.ne.jp>
@ 2001-10-30 17:57 ` Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 0 replies; 5+ messages in thread
From: Masaru Sugawara @ 2001-10-30 17:57 UTC (permalink / raw)
To: pgsql-sql
On Sat, 06 Oct 2001 18:54:21 +0900
Masaru Sugawara wrote:
>
> A B C D select?
> ------------------------------------
> 1 FOO A1 100 n
> 1 BAR Z2 100 n
> 2 FOO A1 101 y
> 2 BAR Z2 101 y
> ---------------------------------
> 5 FOO A1 99 n
> 3 FOO A1 102 y
> ---------------------------------
> 6 BAR Z2 98 n
> 4 BAR Z2 99 y
> ---------------------------------
> 7 FOO AB 103 y
> 7 BAR ZY 103 y
>
>
> select u0.A, u0.B, u0.C, u0.D
> from (select t0.*, t1.cnt
> from (select a, count(*) as cnt
> from test_table
> group by a ) as t1
> inner join test_table as t0
> on(t0.a = t1.a)
> ) as u0
> where not exists (select u1.*
> from (select t0.*, t1.cnt
> from (select a, count(*) as cnt
> from test_table
> group by a ) as t1
> inner join test_table as t0
> on(t0.a = t1.a)
> ) as u1
> where u1.cnt = u0.cnt
> and u1.a != u0.a
> and u1.d > u0.d
> and u1.b = u0.b
> and u1.c = u0.c
> )
> ;
I noticed there were two vain subselects in the query
when I had checked past queries by an EXPLAIN, and
gave a small change to the query.
-- on 7.1.2
select u0.*, u1.cnt
from (select a, count(*) as cnt
from test_table group by a ) as u1
inner join test_table as u0 on(u0.a = u1.a)
where not exists (select t0.*, t1.cnt
from (select a, count(*) as cnt
from test_table group by a) as t1
inner join test_table as t0 on(t0.a = t1.a)
where t1.cnt = u1.cnt
and t0.a != u0.a
and t0.d > u0.d
and t0.b = u0.b
and t0.c = u0.c
)
;
Masaru Sugawara
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-10-30 17:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-03 17:33 challenging query Chris Ruffin <c.ruffin@ieee.org>
1970-01-01 00:00 ` Haller Christoph <ch@rodos.fzk.de>
1970-01-01 00:00 ` Haller Christoph <ch@rodos.fzk.de>
2001-10-06 09:54 ` Masaru Sugawara <rk73@echna.ne.jp>
2001-10-30 17:57 ` Masaru Sugawara <rk73@echna.ne.jp>
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