pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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