Hi Khaleel,
Mind if you try the following query? I think it's the same semantics as you
meant for your query.
select distinct W."rowId", W."md5_current", W."size_current" from "Wiki" AS
W
where
W."rowId" in (select H"rowId" from "History" AS H
where H."timestamp" =
(select MAX(H2."timestamp") from "History"
AS H2 where H2."rowId" = H."rowId")
AND
H."status" = 'approved')
Thanks,
Maryann
On Fri, Apr 24, 2015 at 5:25 PM, khaleel mershad <khellom...@gmail.com>
wrote:
> 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
>>>>>
>>>>
>>>>
>>>
>>
>
