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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])