pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@echna.ne.jp>
To: pgsql-sql@postgresql.org
Subject: Re: challenging query
Date: Sat, 06 Oct 2001 18:54:21 +0900
Message-ID: <20011006181844.7E29.RK73@echna.ne.jp> (raw)
In-Reply-To: <200110051503.RAA01444@rodos>
References: <579d73bf.0110030933.22b6d76e@posting.google.com>
	<200110051503.RAA01444@rodos>

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




view thread (5+ messages)  latest in thread

Message-ID: <20011006181844.7E29.RK73@echna.ne.jp>
Permalink:  ../20011006181844.7E29.RK73@echna.ne.jp/
Also on:    postgresql.org/message-id/20011006181844.7E29.RK73@echna.ne.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@echna.ne.jp
  Subject: Re: challenging query
  In-Reply-To: <20011006181844.7E29.RK73@echna.ne.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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