Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f969pKn91326 for ; Sat, 6 Oct 2001 05:51:20 -0400 (EDT) (envelope-from rk73@echna.ne.jp) Received: from VIP333 [210.175.55.149] by echna.ne.jp with ESMTP (SMTPD32-6.06) id A41322180252; Sat, 06 Oct 2001 18:51:15 +0900 Date: Sat, 06 Oct 2001 18:54:21 +0900 From: Masaru Sugawara To: pgsql-sql@postgresql.org Subject: Re: challenging query In-Reply-To: <200110051503.RAA01444@rodos> References: <579d73bf.0110030933.22b6d76e@posting.google.com> <200110051503.RAA01444@rodos> Errors-To: Message-Id: <20011006181844.7E29.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.06 X-Archive-Number: 200110/101 X-Sequence-Number: 5056 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