Re: challenging database query

c.ruffin_at_ieee.org (Chris Ruffin) wrote in message news:<579d73bf.0110030858.2a451e8f_at_posting.google.com>...
> 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
>
>
> I want to select all of the numbers A which define distinct groups and
> have the highest datetime D. Is this possible using a single query?

select distinct a.A from following a
where not exists ( select b.A from following b where b.D > a.D

                  and (select count(*) from following c where c.A =
b.A)
                    = (select count(*) from following d, following e
                          where d.A = a.A
                          and   e.A = b.A
                           and  d.B = e.B
                           and  d.C = e.C )

                   )

This looks for sets a that don't have sets b that match a and have later timestamps. No guarantees on performance; it would help if the following table were normalized. Received on Fri Oct 05 2001 - 03:09:34 CEST