Re: challenging query - Mailing list 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
followingtable 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 | 1012 | FOO | A1 | 1013 | FOO | A1 | 1024 | BAR | Z2 |  997 | BAR | ZY | 1057 | FOO |
AB| 105
 
(6 rows)


Have a nice weekend!


----------------------
Masaru Sugawara