Re: challenging query - Mailing list pgsql-sql

From Haller Christoph
Subject Re: challenging query
Date
Msg-id 200110050948.LAA01172@rodos
Whole thread Raw
In response to challenging query  (c.ruffin@ieee.org (Chris Ruffin))
List pgsql-sql
> 
> 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?

Sorry, there is an inconsistency in your question, at least to me. 
In the table's last column you say you want to select 
A in (2,3,4,7) 
but in the epilogue you say you want to select 
A in (7,1,1,3) 
What did I miss? 
Regards, Christoph 


pgsql-sql by date:

Previous
From: Markus Bertheau
Date:
Subject: Re: strange query execution times
Next
From: Janning Vygen
Date:
Subject: Problem with n to n relation