Dear Maryann, Thanks for your question. You are right: the query that I was writing wasn't the correct one for my purpose. The query that will satisfy my request would be:
select distinct W."rowId", W."md5_current", W."size_current" from "Wiki" AS W where EXISTS (select * from "History" AS H where (H."rowId" = W."rowId") AND H."timestamp" = (select MAX(H2."timestamp") from "History" AS H2 where (H2."rowId" = W."rowId")) AND (H."status" = 'approved') ) In this query I specify that I need to select the exact version which has the Maximum timestamp among all versions of the same data item by using MAX(H2."timestamp") within the inner subquery within EXISTS. However I tried such query and it still produces the same error as the old query, which is: (Aggregate may not contain columns not in GROUP BY.) Thank you for your help. Best, Khaleel On Thu, Apr 23, 2015 at 5:49 PM, Maryann Xue <maryann....@gmail.com> wrote: > Hi Khaleel, > > Thanks for the explanation! But my question was since this is an EXISTS, I > assume if there ever is a H."timestamp" with an 'approved' status, the > EXISTS will return true regardless of whether you are testing H."timestamp" > or Max(H."timestamp"). Is that correct? or have I missed something? > > > Thanks, > Maryann > > On Thu, Apr 23, 2015 at 8:49 AM, khaleel mershad <khellom...@gmail.com> > wrote: > >> >> Hello Maryann, >> >> Thanks very much for your reply. Hopefully this bug gets fixed in the >> next release so that I can continue working with this part in my research >> project. Thanks for keeping me posted. >> >> With respect to your question, I am using the "History" table as a data >> store of all versions of a certain data item. When I say Max( >> H."timestamp") inside the query, I am selecting the latest version (most >> recent) that is "approved" (which is checked using the condition H."status" >> = 'approved') >> >> >> >> Best Regards, >> Khaleel >> >> On Thu, Apr 23, 2015 at 6:22 AM, Maryann Xue <maryann....@gmail.com> >> wrote: >> >>> Hi Khaleel, >>> >>> Thanks a lot for reporting the problem, which looks like a bug. I will >>> file a JIRA and keep you posted. >>> >>> One question though, why would we use MAX(H."timestamp") instead of >>> H."timestamp"? >>> What difference would it make? >>> >>> >>> Thanks, >>> Maryann >>> >>> On Wed, Apr 22, 2015 at 9:45 AM, khaleel mershad <khellom...@gmail.com> >>> wrote: >>> >>>> >>>> Hello, >>>> >>>> I tried executing the following query in Phoenix: >>>> select distinct W."rowId", W."md5_current", W."size_current" from >>>> "Wiki" AS W where EXISTS( select MAX(H."timestamp") from "History" AS H >>>> where (H."rowId" = W."rowId") AND (H."status" = 'approved') ); >>>> >>>> I got the following error: >>>> ERROR 1018 (42Y27): Aggregate may not contain columns not in GROUP BY. >>>> (state=42Y27,code=1018) >>>> >>>> If I replace W."rowId" (in the where clause) with a constant value (for >>>> example: '3587'), the query works fine. Also, if I replace the aggregate >>>> MAX(H."timestamp") with H."timestamp", the query also works fine. >>>> >>>> So it seems that Phoenix generates error when using a reference to an >>>> outer query while using an aggregate within the inner query. >>>> >>>> Any solutions? >>>> >>>> >>>> >>>> Regards, >>>> >>>> Khaleel Mershad, Ph.D. >>>> Research Associate >>>> >>>> American University of Beirut >>>> Department of Electrical and Computer Engineering >>>> Bliss Street, Beirut, Lebanon >>>> email: kw...@aub.edu.lb >>>> >>> >>> >> >
